DCount() criteria

[Guest]

Hi All,

I have a query based on Staff Timesheets that I require to count the number
of occurences of a certain letter (e.g. "S" for sick). The range of dates
that the totals need to be calculated over differs for each employee
depending on the type of contract they are on. This requires that the
DCount() function criteria needs to specify only to count between certain
dates for each employee. As it stands I have:

DCount("[AM]","Timesheet Query","[EmployeeID]=" & [Employee ID] & " AND
[Date:]>=" & [Date From] & " AND [Date:]<" & [Date To] & " AND [AM]='S' ") AS
TotS

Where the [Employee ID] in my query is referenced fine but the two dates
([Date From] and [Date To]) don't seem to have any influence. Is there any
reason for this? Or is my code slightly out?

Thanks in advance for any help offered!
Dan

 

[John Spencer]

Dates must be delimited by # marks when you are using literals AND they must
be in US format mm/dd/yyyy or in yyyy-mm-dd format. If your date format is
set to the mm/dd/yyyy format, then the following should work.

DCount("[AM]","Timesheet Query",
"[EmployeeID]=" & [Employee ID] &
" AND [Date:]>=#" & [Date From] &
"# AND [Date:]<#" & [Date To] &
"# AND [AM]='S' ") AS TotS

This version is less sensitive to the date settings of your operating system
DCount("[AM]","Timesheet Query",
"[EmployeeID]=" & [Employee ID] &
" AND [Date:]>=" & Format([Date From],"\#yyyy/mm/dd\#" &
" AND [Date:]<" & [Format([Date To],"\#yyyy/mm/dd\#" &
" AND [AM]='S' ") AS TotS