Date Select statement not working from 01/10/2004

Date Select statement not working from 01/10/2004

Post by SGVsZ2FyZ » Sat, 02 Oct 2004 21:07:02


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?
 
 
 

Date Select statement not working from 01/10/2004

Post by Douglas J. » Sat, 02 Oct 2004 21:14:46

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://www.yqcomputer.com/ ~allenbrowne/ser-36.html or what I have in my
September, 2003 Smart Access column at
http://www.yqcomputer.com/




textbox
(RA_DateReceived)
=
the

 
 
 

Date Select statement not working from 01/10/2004

Post by SGVsZ2FyZ » Sat, 02 Oct 2004 21:37:02

Thanks

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

Date Select statement not working from 01/10/2004

Post by Douglas J. » Sat, 02 Oct 2004 22:17:51

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\#)





running

as dd
at
my


(RA_TimeReceived)
is
for
Null.