SQL dosen't pick up local date setting! Is MS going to fix this?

[Guest]

I don't know if this is a bug, but i have encountered this before. Can some
one at microsoft fix this!

When designing a query , and in the SQL window

if you have a select , you enter in the SQL window with a where clause YOU
MUST USE american date format!? #mm/dd/yyyy# regardless of your local
setting! ALSO if you are coding in VBA you fall into the same trap. This
fails..

sWhere = "[Required By] <= #" & CStr([ToDate]) & "#"

This works

sWhere = "[Required By] <= #" & Format(ToDate, "mm/dd/yyyy") & "#"

Is this a bug ? and is MS intending on fixing it? I think that it should
pick up the local setting as in most places you don't have to think about
this! This is very annoying!


mike

 

[MGFoster]

That's not a bug. It is described in the documentation.

If you want you can use CDate():

WHERE date_column = CDate(Forms!myForm!txtFromDate)

CDate() is supposed to convert all valid date expressions to the date's
numeric value (a Double).

 

[John Spencer]

Another alternative is to use the unambigous (and slightly unnatural) format
of yyyy/mm/dd

That's not a bug. It is described in the documentation.

If you want you can use CDate():

WHERE date_column = CDate(Forms!myForm!txtFromDate)

CDate() is supposed to convert all valid date expressions to the date's
numeric value (a Double).
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

I don't know if this is a bug, but i have encountered this before. Can
some one at microsoft fix this!

When designing a query , and in the SQL window

if you have a select , you enter in the SQL window with a where clause
YOU MUST USE american date format!? #mm/dd/yyyy# regardless of your local
setting! ALSO if you are coding in VBA you fall into the same trap. This
fails..

sWhere = "[Required By] <= #" & CStr([ToDate]) & "#" This works

sWhere = "[Required By] <= #" & Format(ToDate, "mm/dd/yyyy") & "#"

Is this a bug ? and is MS intending on fixing it? I think that it should
pick up the local setting as in most places you don't have to think about
this! This is very annoying!