Access 97 UK/US Date Formats

[AdeH]

I'm trying to use the WHERE condition of the docmd.openreport action which requires dates to be in US format (mm/dd/yy). As I and my users are in the UK, the system settings are all UK (dd/mm/yy).

I'm using the following to pass US formatted dates to my WHERE statement:

Format (me.txtFrom, "mm/dd/yy")

but for some reason it only formats to US format if the day part is below 10 (i.e. 06/05/01 (6th May 01) converts fine to 05/06/01, but 26/05/01 (26th May 01) stays as 26/05/01).

Can anyone help me on this, it's driving me nuts!!!!

[charlotte]

have you tried using the CDate() function to convert the value into a date instead of using format? CDate should convert it to whatever format your system uses.

[AdeH]

Yeah, I tried that but as my PC locale setting is English (UK), it sets it to dd/mm/yy format (rather than mm/dd/yy). Still driving me nuts!

[AdeH]

Eureka, have fixed it.....

Rather than formatting my date variable i.e.
datFrom = Format(me.txtFrom,"mm/dd/yy")

If I pass the unformatted datFrom to my SQLstring and Format it as part of that it seems to work i.e. Format (datFrom,"mm/dd/yy").

Thanks for your help anyway,

Cheers

Ade