DCount problem

[ZXDAN]

Code:
Dim CheckDate As Long
CheckDate = DCount("[ACTUALDATE]", "tblDATEOpen", "[ACTUALDATE] = #" & Forms!frmAddIBTData_local!IBTDATE & "#")
txtCheckDate.Value = CheckDate
Code/

The above code keeps returning a '0'...it was working for several weeks and now suddenly stopped...even though tblDATEOpen does contain the required value!?

I'm stumped...help!
When I set the date to 12/03/2012 it returns a '0' but whe I change it to 14/03/2012 it returns a '1' (the number of occurances in tblDATEOpen) however both dates occur in the table..very strange..

 

[spikepl]

Not really strange, 14/03/2012 is not ambiguous, 12/03/2012 is.

http://allenbrowne.com/ser-36.html

 

[ZXDAN]

Thanks Spikepl

You are absolutely correct of course but how to fix it...following Mr Browne's advice I have set my regional setting to dd/MM/yyyy however the problem continues...

I notice he says "delimit dates with the "#" symbol. Regardless or your regional settings, Access expects these literal dates to be in the American format, e.g. #12/31/1999#."

So I now need to get round that...need I format the date criteria?
CheckDate = DCount("*", "tblDATEOpen", "[ACTUALDATE] = #"Format( & Forms!frmAddIBTData_local!IBTDATE & ,"dd/MM/yyyy")"#")

 

[JANR]

You have two choices, either format the date outside the string to an uambigious dateformat or use the full form refrence to your datecontrol inside the expression.

Either:

CheckDate = DCount("*", "tblDATEOpen", "[ACTUALDATE] =" & Format(Me.IBTDATE ,"\#mm\/dd\/yyyy\#"))

Or:

CheckDate = DCount("*", "tblDATEOpen", "[ACTUALDATE]=Forms!frmAddIBTData_local!IBTDATE")

JR

 

[ZXDAN]

Thanks JANR
I tried the first one and it worked perfectly... I'll have to look into this as I don't really understand it. However I read another post where a developer stored all his dates yyyymmdd as a double ...linked to a calender you could show the date in any format you liked but use the double as your index...

Thanks again for your help.

 

[rodmc]

Note that JET SQL, no matter what your regional setting are treats them as US dates, is that not correct? is this why you are having a problem do you think