Strange Behaviour (1 Viewer)

[welshpunk]

Hi All

I have just noticed some strange behaviour in access. I hava and asp page which gets two dates which are then used in a sql statement to refresh a page. The end of the statement deals with a between clause like this

WHERE tbl_Movements.date_of_Movement Between #12/03/2007# AND #30/03/2007#

But when I view this in design mode it changes it to

Between #03/12/2007# And #30/03/2007#

Why does it do this and what can I do to resolve it

Thanks

 

[neileg]

SQL is an American invention. It likes its dates in US format. Most of the time, Access manages to translate other international formats to US on the transfer to SQL (but not always!). Because 12/03/2007 can be translated to a valid date in US terms, it does. But 30/03/2007 can't so its left alone.

I usually use a medium date format like 12-Mar-2007 which removes any ambiguity.