Date Select statement not working from 01/10/2004

[Guest]

The following query worked until today. My date format is ShortDate
(dd/mm/yycc)
The value returned from the table is 01/10/2004 and the value in the textbox
is 01/10/2004.

QryCurRA = "SELECT * From RA Where (RA_System_LU) = 1 And (RA_DateReceived)
= " + "#" + Str(Tbx_RA_DateReceived.Value) + "#" + " And (RA_TimeReceived) =
" + "#" + Str(Tbx_RA_TimeReceived.Value) + "#" + ""

If I run a query and put the date in as 10/01/2004 (10 jan 2004) then is
select the data from the 1st October. When I enter data into my table for the
10/01/2004, it does not select October anymore and my query returns Null.

How do I go about resolving this?

 

[Douglas J. Steele]

Regardless of what your Short Date format has been set to in Regional
Settings, you MUST use either mm/dd/yyyy or an unambiguous format such as dd
mmm yyyy or yyyy-mm-dd.

You may want to read "International Dates in Access", by Allen Browne, at
http://members.iinet.net.au/~allenbrowne/ser-36.html or what I have in my
September, 2003 Smart Access column at
http://members.rogers.com/douglas.j.steele/SmartAccess.html

 

[Guest]

Thanks

Changed my regional setting for date to dd/MMM/yyyy and I am up and running
again.

 

[Douglas J. Steele]

Glad you got it going, but recognize that all you did was relieve the
symptom, not fix the problem.

Should your application be used by someone else, you'll run into the same
problem, and, in my opinion, forcing the user to change his/her regional
settings is NOT an acceptable solution.

A more generic solution would be:

QryCurRA = "SELECT * From RA Where (RA_System_LU) = 1 And (RA_DateReceived)
= " & Format(CDate(Tbx_RA_DateReceived.Value), "\#mm\/dd\/yyyy\#") & " And
(RA_TimeReceived) =" &
Format(CDate(Tbx_RA_TimeReceived.Value),"\#mm\/dd\/yyyy\#)