Problem when using DCOUNT on Dates (1 Viewer)

Hi guys, I'm having a problem when trying to DCOUNT the number of total records in a table, containing a specific date in a Date/Time field in this table.

The code I'm trying to use and that used to work for me is this:


I've been using this code so far and worked nicely, but I don't understand why I'm having a problem on this particular example.
When debugging on the inmediate window, I get that dDate = #04/11/2019#, which is correct, but the DCOUNT is always 0, while in this table it exists records with this specific date.

Also the field in the table is set up as Date/Time and the format of it as Short Date.

Any clue or suggestion on how I could do this in any other way? Or why is not working with this particular example when it used to work for me? I tried several ways of doing this and I can't get to it.

Thanks for reading

Try using [DATE], but you should not really use reserved words anyway.?

Yet you say it has worked fine before?

Even though the format of the records is set to short date, if they actually have a time value that is anything other than 00:00:00 then your count won't match as Date() would equate to 04/11/2019 00:00:00 .

And as Gasman said, using Date as a field name WILL give you problems.

Finally, where are you - UK? might be worth formatting the date to avoid any ambiguities.

In addition to the above comments, you need to use Dim dDate As Date

Note there was a missing ) in Minty's code - now fixed

Hi guys, thanks for all your help, I ended up solving this using this expression:


Not sure why, it only worked for me using english dates. Indeed I'm not from UK but I work with a english version of Access, so maybe that is why.

I appreciate very much all your help!

Teggun said:

Hi guys, thanks for all your help, I ended up solving this using this expression:

Code:Copy to clipboard

DCount("*", "tblWhatever", "[Date] = #" & Format(Date, "mm/dd/yyyy") & "#")

Not sure why, it only worked for me using english dates. Indeed I'm not from UK but I work with a english version of Access, so maybe that is why.

I appreciate very much all your help!

Click to expand...

Hi. Maybe this article will explain why. Cheers!