DCount doesn't find date record

[Ajabu]

Hi All,
I’m having some problems filtering for a date with DCount. My regional settings are set to European date format (dd/mm/yyyy). In my final code I'm using a variable for the date, but to simplify things I'm just putting an actual date in the examples below.

If I look for the date number, it works:

DCount("*", "tbl_Holidays", "HolidayDate = 42729")

The following also works, though the date is converted to the wrong format (US settings: mm/dd/yyyy). This means 01/06/2016 would be changed to 06/01/2016 and the record wouldn't be found.

DCount("*", "tbl_Holidays", "HolidayDate = #25/12/2016#")

As per suggestions in other threads, I’ve tried the following. But none of these work:

DCount("*", "tbl_Holidays", "HolidayDate = #" & Format(25/12/2016, "dd/mm/yyyy") & "#")
DCount("*", "tbl_Holidays", "HolidayDate = " & Format(25/12/2016, "\#dd/mm/yyyy\#"))
DCount("*", "tbl_Holidays", "HolidayDate = " & CDbl(25/12/2016))

Can someone tell me what I’m doing wrong? Thanks for your help.

 

[JHB]

The criteria must be converted to the US format = "mm/dd/yyyy" regardless of what you local setting is.

 

[Ajabu]

I changed the code to the following:

DCount("*", "tbl_Holidays", "HolidayDate = #" & Format(12/12/2016, "mm/dd/yyyy") & "#")
DCount("*", "tbl_Holidays", "HolidayDate = " & Format(12/12/2016, "\#mm/dd/yyyy\#"))

It made no difference. Still doesn't work.

 

[JHB]

Try the below:

..."HolidayDate =#" & Format([B][COLOR=Red]"[/COLOR][/B]12/12/2016[B][COLOR=Red]"[/COLOR][/B],"mm/dd/yyyy") & "#")

Else post some sample database, zip it
Do you get any error message, the show it.

 

[Ajabu]

It works! Thanks so much.

 

[JHB]

You're welcome, good luck.