DCount Not Working

[Cavman]

Help - my DCount's not working! Here it is:

 Me.txtOrdersToday = DCount("[AccountNo]", "tblAccountBase", "[UserID] = " & UserID & " AND [AccountOrderedFocusThisP] = TRUE AND [StatusDate] = #" & Date & "#")

This DCount returns '0', however, the following query returns '2', which is correct:

 SELECT Count(tblAccountbase.AccountNo) AS CountOfAccountNo, tblAccountbase.UserID, tblAccountbase.AccountOrderedFocusThisP, tblAccountbase.StatusDate
FROM tblAccountbase
GROUP BY tblAccountbase.UserID, tblAccountbase.AccountOrderedFocusThisP, tblAccountbase.StatusDate
HAVING (((tblAccountbase.UserID)=41) AND ((tblAccountbase.AccountOrderedFocusThisP)=True) AND ((tblAccountbase.StatusDate)=Date()));

Can anyone tell me why the query works but the DCount doesn't? I've set a breakpoint and the UserID is definitely '41' and the DCount works if I omit the Date criteria at the end so I'm guessing it could be something to do with the date criteria format, but I've tried changing them still with no luck.

Thanks,

Simon

 

[pr2-eugin]

Hmmm.. Might be because the format are a bit different.. If you use ## the system automatically converts it to mm/dd/yyyy.. Try this..

Me.txtOrdersToday = DCount("[AccountNo]", "tblAccountBase", "[UserID] = " & UserID & " AND [AccountOrderedFocusThisP] = TRUE AND [StatusDate] = " & CDbl(Date) & "")[B]

[/B]

 

[Cavman]

Brilliant, that worked Paul! Thanks

 

[pr2-eugin]

You are welcome Cavman.. Glad to Help.. Good day..