DCount with date criteria (1 Viewer)

[choaspes]

Dear All

I'd be very grateful for a steer on how to get the DCount function working with date critera. The idea is to have a function on a form that checks that the user isn't entering a record that already exists on [tbl], by comparing the date being entered on a form with dates that already appear within the table's existing records.

DCount("[ID]", "[tbl]", "[datefield] = #" & Me!Textbox.Value & "#")

For some reason the code above always returns zero, even where the value being entered in the textbox is identical to a value of [datefield] in an existing record in [tbl]. What am I doing wrong?

 

[arnelgp]

use english date format:

DCount("[ID]", "[tbl]", "[datefield] = #" & Format(Me!Textbox.Value, "mm/dd/yyyy") & "#")

 

[choaspes]

MsgBox (DCount("[ID]", "[tbl]", "[datefield] = #" & Format(Me!Textbox.Value, "mm/dd/yyyy") & "#") ) returns

Run time error 6:

Overflow

I'm attempting to compare time as well - both the textbox and table field are date and time, does that make a difference?

 

[choaspes]

My apologies, total idiot - typo in the Format line!

It works! Thank you