DCount multiple criteria (date range and text field)

[KenR]

I am trying to count the number of records in a table that meet a specified
date/time range in one field (EntryDate) and the username in a second field
(EntryBy).

'***********
Dim dtMin As Date
Dim dtmax As Date
Dim stUser As String
Dim intArrived As Integer
stUser = CurrentUser()
dtMin = #8/28/2008 9:05:01 AM#
dtmax = #8/28/2008 9:59:59 AM#
intArrived = DCount("*", "[Details]", "[EntryDate] Between #' & dtMin & '#
And #' & dtmax & '#' And [EntryBy] = stUser)
'******************

I get a syntax error when I try it, and can't figure out the correct syntax.

I'm using these static values in dtMin and dtMax for testing purposes; the
live code uses dMin and dMax functions to determine live values.

"Details" is the table
"EntryDate" is a date/time field with a general date format
"EntryBy" is a text field with 15 characters max

This code:
*********
intArrived = DCount("ID", "Details", "ShortEntryDate Between #" & dtMin & "#
And #" & dtmax & "#")
*********
works fine , but doesn't add the second criterion (current user) to find the
correct records, so the count includes records entered in that date range by
another person.

Can anyone help me with the syntax?

Thanks

KenR

 

[dch3]

Try something like...

intArrived = DCount("ID", "Details", "ShortEntryDate Between #" & dtMin & "#
And #" & dtmax & "#" & " AND [userNameField] = '[userName]'")

The WHERE statement is literally a SQL WHERE statement that allows ANDs, ORs
and other whatnot just keep adding the critiera as needed.

 

[Klatuu]

intArrived = DCount("*", "[Details]", "[EntryDate] Between #" & dtMin & "#
And #" & dtmax & "# And [EntryBy] = """ stUser & """")