Some dates not recognised in DCount!!!

[Lee]

Hi all,
I'm testing a DCount in the Debug window to try and see
why it's not returning the expected values. Here's the
code in the Immediate Window:
DCount
("[CentreNo]", "tbl_InspectionReports", "[InspectionDate]
= #01/05/2004#")

The InspectionDate field within the table is set to a
Date/Time type and I've set it to display in Short Date
format. I've put in 3 records with the above date and 0
is returned. If I replace each of the three records with
26/02/2004 it works fine!!!!??? I've tried putting an
InputMask in the table, I've tried various date formats
so why on earth doesn't it work??!!! I'm going nuts here!

Hope you can help.

Regards,

Lee

 

[Peter Hoyle]

Hi Lee,

I usually use the US format like #2/26/2004# for 26th Feb 2004
or #1/5/2004# for 5th January 2004

and just use the following function to convert for me

Function MakeUSDate(x As Variant)
If Not IsDate(x) Then Exit Function
MakeUSDate = "#" & Month(x) & "/" & Day(x) & "/" & Year(x) & "#"
End Function

Cheers,
Peter

 

[Lee]

Yep, this is a good idea!!! I thought (wrongly!) that if
the Regional Settings were UK style then the code would
follow but obviously not. Thanks for the suggestion -
all is OK now!

Best wishes,

Lee

-----Original Message-----
Hi Lee,

I usually use the US format like #2/26/2004# for 26th Feb 2004
or #1/5/2004# for 5th January 2004

and just use the following function to convert for me

Function MakeUSDate(x As Variant)
If Not IsDate(x) Then Exit Function
MakeUSDate = "#" & Month(x) & "/" & Day(x) & "/" & Year(x) & "#"
End Function

Cheers,
Peter

Hi all,
I'm testing a DCount in the Debug window to try and see
why it's not returning the expected values. Here's the
code in the Immediate Window:
DCount
("[CentreNo]", "tbl_InspectionReports", "[InspectionDate]
= #01/05/2004#")

The InspectionDate field within the table is set to a
Date/Time type and I've set it to display in Short Date
format. I've put in 3 records with the above date and 0
is returned. If I replace each of the three records with
26/02/2004 it works fine!!!!??? I've tried putting an
InputMask in the table, I've tried various date formats
so why on earth doesn't it work??!!! I'm going nuts here!

Hope you can help.

Regards,

Lee

.