dates in query HELP PLEASE!

[Jorge Novoa]

Hello everyone.
I have a little question:

I got some controls in a form, I'm trying to run a query with the values of
these control, of course. Both are date values,
I create a where string clause, which goes like this:
strWhereSql= "WHERE Retts.Date between #" & me.datStart & " AND " &
me.datEnd

Then I append the where string to the rest of the SQL string, in order to
run it later. No problem or doubt with that.

I'm using Access XP in Spanish, windows 98 in Spanish, the system settings
for date is: DD/MM/YYYY

The string created is like this:
WHERE Retts.Date between #11/05/2004# AND #11/05/2004#

You see? It has to be May 11, 2004 for me
BUT Access returns me the data for November 5, 2004 !!!!!!!
It turns the days to month, and the mont to days
WHY???

Another thing: if I try with 20/05/2004, that is May 20, 2004 for me, for
example, it returns the data for May 20, 2004.!!!!¿?¿?¿?!!!! ¿?¿¿?!!!!
WHY???

Why does Access 'convert' the date as it please???

All my formats, all my settings are dd/mm/yyyy, but access keep using the
mm/dd/yyyy format when running a query.

I've also tried: format(datStart, "dd/mmm/yyyy"), but it' doesn't work

I'm amazed how access change these values, I know it has something to be
with the language, I Only need to know why is this situation caused, and how
to correct it.

PLEASE HEEEELP


Thanx!

 

[Douglas J. Steele]

Regardless of what your regional settings have the short date format set to,
you must use mm/dd/yyyy in your queries. Access will always translate your
date to mm/dd/yyyy format if that makes sense. Since there is no 20th month,
it treats 20/05/2004 as 20 May, but since there is a 10th month, it will
treat 10/05/2004 as 05 October, not as 10 May.

Try:

strWhereSql= "WHERE Retts.Date between " & _
Format$(me.datStart, "\#mm\/dd\/yyyy\#") & _
" AND " & Format$(me.datEnd, "\#mm\/dd\/yyyy\#")

You may find Allen Browne's "International Dates in Access" at
http://members.iinet.net.au/~allenbrowne/ser-36.html to be informative, or
check out what I have at
http://members.rogers.com/douglas.j.steele/SmartAccess.html

 

[ChrisJ]

Another idea is to format the date as

"dd mmm yyyy" or "mmm dd yyyy" which gives
05 Nov 2004 or May 11 2004

This way access gets an unambiguous date and cannot get it
wrong.

 

[Jorge Novoa]

Thanx a lot!