Short Date Format 1

[TechnicalAnalysis]

I have a query like this:

INSERT INTO Table2 ( fields1, field2,.... ) SELECT Table1.fields1, Table1.fields2,.... FROM Table2 WHERE [Table2]![Fields3]>=#1/6/2002# And [Table2]![Fields3]<=#30/6/2002#
[Table2]![Fields3] is a Date field with Short Date format

Both Dates in the WHERE portion above are generated by the DateSerial function of Access.
For example &quot;........ FROM Table2 WHERE [Table2]![Fields3]>=#'&quot; & DateSerial(2002,6,1) & &quot;# AND [Table2]![Fields3]<=#&quot; & DateSerial(2002,6,1) &&quot;#&quot;

It works fine if the date format of my PC is m/d/yyyy
It doesn't pull the right records if the PC date format is d/m/yyyy
Is it possible to make it works on PC with different date format set up. m/d/yyyy and d/m/yyyy

Thanks

 

[cheerio]

The & operator simply joins two strings together.

What you are doing is trying to use it to join a string to a date. This forces an implicit conversion of the date to a string using local Windows settings. It is not relevant how the datefield might be formatted when you look at the table.

The answer is to avoid the implicit conversion and use an explicit one like:

& format(somedate,&quot;mm/dd/yyyy&quot

Now you know how the date will be converted and are not dependent on how a particular machine has been configured. Obviously you use a format string that meets your needs.

Ken

 

[TechnicalAnalysis]

Thanks Cheerio
I tried and it works fine.