Not sure if this belonged in reports or queries, so I chose general. I have looked at several DCount threads but haven't quite found my answer. I want to use Dcount in an unbound textbox in a report. It counts the number of records in another table - comparison
the first part of the statement works fine ( up to 'iss'"). When i added the between date part i'm not getting any # returned in my report. I want to addthe criteria of RecDate between the 2 dates on the open form.
Can anyone tell me where my problem lies? ( if this makes sense)
Thanks
Kevin
=DCount("[type]","comparison","[type] Like 'iss*'" And ("[comparison].[RecDate]" Between Forms!DateInputforRMAsReturned!Text0 And Forms!DateInputforRMAsReturned!Text2))
The following code is giving me a "Run-Time error '13' Type mismatch. I have tried isolating both criteria and they seem to be fine but joined together with "AND" they error. Workdate is a Shortdate. Flightnumber and flightID are numbers. FlightID source is a cmb within my form.
Private Sub FlightID_BeforeUpdate(Cancel As Integer)
If DCount("[WorkDate]", "Main_tbl", "[WorkDate]= #" & Me.WorkDate & "#" And "[FlightNumber] =" & Me.FlightID.Column(0)) > 0 Then Do this.... End If
I used a DCount however this does not separate the employees. Currently using Count Of Visits: DCount("[Duration]","Test Query","[Duration]<20 AND [Employee ID] = [Employee ID]")
I have a field named Poisition_Id and a yes/no field with the name Hired, what i want is dcount under condition initially with Position_ID to Position_ID then to see the field of hired if "yes"
Example:
=dcount "a" see in tbl_open with criteria position_id = position_id with criteria hired=yes
I can't get the following DCOUNT working for the life of me: DCount("EmployeeName", "tblAttendance", "EmployeeName = '" & cboEmployee Name. Value & "'" And "AttendanceDate = #" & txtAttendanceDate.Value & "#")I have checked this forum along with Google and there seems to be no easy way to do this? I have tried editing the syntax over and over again. I have been able to run a query and get the desired result in that query; however I am unable to call that result in VBA.
With a form with two fields one is a date field formatted as short date and the other is a lookup combo box that I use to lookup predefined times and is formatted as short time. The date and time are stored in the same table as date/time in separate fields. I attempted to use Dcount, and get syntax error missing operator. Below is one of a hundred that i have tried after scouring this forum and the net. I ended up converting the Appt_Time field to a text field in the table to eliminate one date field but still got no where.
Code: Private Sub Appt_Time_BeforeUpdate(Cancel As Integer) 'Check table for duplicate If DCount("[Appt_Time]", "tblexams", "Appt_Time= & Appt_Time & And [Sch_Date] = #" & Sch_Date & "#") > 0 Then 'Message box warning of duplication
I am working with MS Access 2003. I have a form (frmCalendar) with a textbox (tbDay) and a combobox (cmbLineName)
I want to count the number of records based on the criteria from cmbLineName, and show the result in tbDate. Inside the Control Source of tbDate I put in the following:
The problem is that the result in tbDate is always 0; regardless of what is chosen in cmbLineName. I know that the record count should not be 0 for all criteria.
DCount("*","[tblLeaveRequests]","Status='Approved' And #" & Format([vDate1],"mm/dd/yyyy") & "# BETWEEN [LeaveStartDate] AND [LeaveEndDate]")
The above code counts all leave with the status of approved on the table [tblLeaveRequests]. Great, but i did forget a critera.
Of the leave it is counting, I want it to only count if the employee the leave is for is in a particular location. which exists on the related table, and not the one dcount is calling.
Is this possible? or will i have to resort to replicated data in [tblLeaveRequests]
I am trying to count the amount of records that were created and closed for last month but I am having problems inserting the correct criteria along with the DCOUNT syntax. DCount("*","obsvnofilterqry","(Date_Closed)=MONTH( Date())").Works fine but figuring out how to get the amount of Date_Closed for last month is proving tricky.
I have a report that I am trying to complete based on several queries. I am trying to count the number of records based on certain criteria and using the following DCount.
=DCount("[Calculated time]","IPG1","[Calculated Time] <= 0.04" And [Ship-to party] In ("SN00207PJZ","SN09162XXX","SN09324XXX"))
I want to count the number of IPG1 records that are under .04 and have the Ship-to party of the ones listed. I have tried everything that I can think of to get it to work but can's seem to get it to. I figure it's something easy but I don't see it.
I'm building a customer management database. Part of which is a table to record details of all members of the main customers family or the household compliment.
I'm currently using a DCount to work out how many people on the table are related to my client, to work out the size of the family.....
Could I also then count the members of the household that are under 16 years old by looking at the DoB from the same household members table?
I realise that I'd need to ask access to calculate age at the same time as working out if they're under 16 and then count them if they have the same Customer ID - which might need a little more than this single function!
=DCount("[Ref number]","main","[open or clsoed] = 'open'")
This looks at a table with a primary Key called Ref Number in a table called main with a field called open or clsoed and looks for the value open only.
I need to specify another fileld, called engineer wheer I match the username =jimmy
Im struggling to add this extra field
So it wiill look , for Open and an engineer called Jimmy in a table called main that has a primary key set !
Can anyone give me some pointers on this simple question ?
I am having an issue using DCount to validate against 3 fields within my database. I have a booking form which contains a Staff member, viewing slot, and Viewing Date which is used to book property viewings.
I want the form to check that the booking doesn't already exist when the process booking button is pressed.
I am using the following statement:
Code: If DCount("*", "Viewing", "[Staff_ID]=" & Me.[Staff_ID] & " AND [Viewing_Period] = " & Me.Viewing_Period & "' AND Viewing_Date = '" & Me.Viewing_Date) & "'" > 0 Then MsgBox "Cannot book, booking already exists", vbCritical End If
I always get the error "Syntax Error (Missing Operator)".
I created a query in Query Builder which contains a DCount with multiple parameters and it runs as it should. I am trying to convert it to VBA, but my inability to put in the quotations marks correctly is frustrating me terribly.
Here is the SQL version from Query Builder:
UPDATE [Daily Status Update Table] SET [Daily Status Update Table].NumberOfChases = DCount("[ChaseOtherID5]","[Chases_View_ALL - TX_Mbr 9 Digit]","[ChaseOtherID5] = 'U - Initial Contact' AND [ChaseStatus] = 'A'"), [Daily Status Update Table].ChaseStatus = "A", [Daily Status Update Table].NewStatus = "A", [Daily Status Update Table].ChaseAssignment2 = "Unscheduled" WHERE ((([Daily Status Update Table].ChaseOtherID5)="U - Initial Contact"));
I need to count records based on multiple criteria from two different tables. I have two tables (i.e. "tblTasks" and "tblTaskHistory"). The tables have a one-to-many relationship based on the "TaskID" field. "tblTasks" has a field called "AssignedTo" and "tblTaskHistory" has a field called "TaskStatus". I need to know how many tasks have been "reopened", the "reopened" status is located in the "TaskStatus" field in "tblTaskHistory". I need this count against a unique listing of employees which can be found in the "AssignedTo" field in "tblTasks".
hi. my name is tim. im having some problems with a query that I am writing. it should be rather simple and involves only one table.
the table is called tblCustomer. The query involves 2 fields: cResort and cWeek. Neither of these fields are unique. Together they make up the primary key.
cResort is populated with a textfield: PF, PV, and GS. cWeek is populated with an integer: 1-52 (the number of weeks in a year)
This is what the query is supposed to do: SELECT tblCustomer.* FROM tblCustomer WHERE cResort DOES NOT EQUAL "PF" AND WEEK is LESS THAN 10
here's where the problem lies, this filters out ALL "PF" and ALL cWeek < 10.
I want PF's with a corresponding cWeek from 1-10. I dont want to see PFs with corresponding cWeek 11-52.
Example: Show me cResorts: PF with corresponding cWeek:1-10. Do NOT show me cResorts: PF with corresponding cWeek:11-52.
I am trying to construct a fairly complex query on the fly. I have it working except for the criteria side of the query which I am having trouble with. It doesn't really matter about the vb side of it because I can alter the construction easy enough. it is more a question of how I get the criteria right.
I'll show you the SQL below first:
SELECT DISTINCTROW tblCandidate.CandidateID, tblCandidate.FirstName & ' ' & [Surname] AS Name, tblCandidate.Telephone, tblCandidate.Mobile, tblCandidateAvailability.Status, tblCandidate.CandidateID AS [Count] FROM ((tblCandidate LEFT JOIN tblCandidateArea ON tblCandidate.CandidateID = tblCandidateArea.CandidateID) LEFT JOIN tblCandidateJobCode ON tblCandidate.CandidateID = tblCandidateJobCode.CandidateID) INNER JOIN ((tblBranchDetail RIGHT JOIN (tblCandidateAvailability INNER JOIN tblBooking ON tblCandidateAvailability.AvailableDate = tblBooking.ScheduleDate) ON tblBranchDetail.BranchID = tblBooking.BranchID) INNER JOIN tblBookingDetail ON tblBooking.ScheduleID = tblBookingDetail.ScheduleID) ON tblCandidate.CandidateID = tblCandidateAvailability.CandidateID WHERE (((tblCandidateAvailability.Status)='Available') AND ((tblBookingDetail.ScheduleDetailsID)=103) AND ((tblCandidateJobCode.JobCode)='DO') AND ((tblCandidateArea.PostCodeID)='HP')) OR (((tblCandidateAvailability.Status)='Available') AND ((tblBookingDetail.ScheduleDetailsID)=105) AND ((tblCandidateJobCode.JobCode)='DO') AND ((tblCandidateArea.PostCodeID)='DO')) OR (((tblCandidateAvailability.Status)='Available') AND ((tblBookingDetail.ScheduleDetailsID)=106) AND ((tblCandidateJobCode.JobCode)='DO') AND ((tblCandidateArea.PostCodeID)='HP'));
The table structuring is fine it is the SQL after the WHERE statement that I am having trouble with.
This query works fine to give me all Candidates that comply with these criteria. The only trouble is I want to only results where the Candidate(s) ALL three set of criteria. I have tried replacing the OR with AND and using extra brackets to enclose the whole WHERE statement to imply I need all criteria met but this reports back no results despite me having test data where a candidate meets all criteria.
I am sure this is not as hard as I am making it but I can't figure it out at all.
I also ideally wanted to show only one record (ie one candidate that meets all instead of three records showing the candidates). This is not so important as I can work this out easily enough, by probably hiding fields I don't want to see and showing only unique values, etc...
ONLY NUMBERS ABOVE (2) (3) AND (6) DO NOT WORK, IF ANYONE COULD GIVE SOME LIGHT ON THIS I WOULD BE VERY GRATEFUL, AS I AM IN THE BLUE ON THIS.:rolleyes:
Wonder if you guys can help me with something. I have a table with about 1200 guests, what I want to do is to search the table base on different criteria (or combination of criteria), namely phone #, name, street name, and postal code. Not everyone has all this info, and their names aren't separted into proper lastname or firstnames (old data).
What I want to do is to be able to type in a person's first name, last name, or both (an maybe other info if the first search wasn't successful).
http://www.psynic.com/files/access.jpg
What should I do to implement this? I was thinking of running 4 different queries, and interesect them into the final query. What do you think?
I am trying to produce a report or query that lists 11 attributes for several projects measured each month over the duration of each project. i.e. It is required to have the attributes for project A listed (horizontally) for months 1,2,3,...etc (vertically) followed by those for projects B for months 1,2,3,4,5,6... etc. Can anyone advice on how to do this please?
I'm working on a purchasing app in access. At this point i'm working on the reporting module. I want a user to be able to fill out a start date (text box), end date (text box), and select a code from a list, hit Run Query, and have it pull up a report listing the date that the selected code was used, between the start and end date, and display other info as well.
The problem i'm having is that i can get the date ranges to work, or the code to work, but not both of them. Here's what i have in my query:
Is there a way to produce one query that will produce several results that display in a report that is generated from a button? The only difference is the criteria.
EX: SELECT tblDownTime.dtDate, tblLine.lineName, Sum(tblDownTime.dtDowntime) AS [Total Time Down] FROM tblMachCent INNER JOIN (tblLine INNER JOIN (tblCategory INNER JOIN tblDownTime ON tblCategory.catID = tblDownTime.catID) ON tblLine.lineID = tblDownTime.lineID) ON tblMachCent.machID = tblDownTime.machID WHERE (tblDownTime.dtDate) Between [Forms]![frmDTGraphs]! And [Forms]![frmDTGraphs]![end])) GROUP BY tblDownTime.dtDate, tblLine.lineName HAVING (tblLine.lineName)="[B]name of line");
criteria being name of line. Choices being line 1 or line 2
What I am looking for is one query to somehow generate the info on the two different lines in two different reports.
Is there a way or do I actually have to write the different reports?
