DCount with Date criteria

[Guest]

Hi,

I'm using the DCount function with a date and a text criteria.

intTask = DCount("EventType","tblEventRecording","EventType = 'Private' AND
FollowUpDate = #" & dtDate & "#")

The tblEventRecording has a field "EventType" and another field
"FollowUpDate". I want to count all the Events of a particular "Type" (such
as 'Private' in the above line of code) and on a particular date.

I'm getting the value if I omit the date criteria, hence my guess is that
the Date Criteria is where the problem is. But, I'm unable to get it right.

Can somebody please tell me where I'm going wrong ?
Thank you.

 

[Douglas J. Steele]

What's your Short Date format set to (in Regional Settings in the Control
Panel)?

If it's set to dd/mm/yyyy, Access is going to have a problem with it for the
first twelve days of each month.

Try:

intTask = DCount("EventType","tblEventRecording", _
"EventType = 'Private' AND " & _
"FollowUpDate = " & Format(dtDate, "\#mm\/dd\/yyyy\#"))

 

[Guest]

Thank you Douglas,

That's bang on target. In fact, after going once again through Allen
Browne's thorough tutorial on "Dates in Access", I realized my folly
immediately and began using his little wrapper function in my db. I often
curse myself for not memorizing what I've read. This is one such occasion.