[AccessD] Date standardization

Hi Doug,

I guess it could have been.  As Stuart says, this is something to watch. 
But it only happens in SQL within VBA, not in queries, and only with 
variables or otherwise referencing form controls or whatnot.  So it would be 
worth looking into, but seems to me like it might not be a factor in the 
scenario you have described.

For example, there is no need for any special provision for 
internationalisation in this case:
CurrentDb.Execute "INSERT INTO Table1 ( DateField ) SELECT AnotherDateField 
FROM Table2"
This will work and get the dates right regardless of the date format 
settings of the databases where the tables are located or sourced.

But here, you have to watch out:
CurrentDb.Execute "INSERT INTO Table1 ( DateField ) SELECT AnotherDateField 
FROM Table2 WHERE AnotherDateField > #" & Me.txtStartDate & "#"
This will only work reliably in a MDY system.  Otherwise you have to 
compensate.  There are various ways people do this.  Stuart mentioned one 
approach.  For myself, I normally do like this...
CurrentDb.Execute "INSERT INTO Table1 ( DateField ) SELECT AnotherDateField 
FROM Table2 WHERE AnotherDateField > " & CLng(Me.txtStartDate)
... which again, will work correctly regardless of international settings 
etc.

But whether this is related to the problem you mentioned, is another 
question.

Regards
Steve


--------------------------------------------------
From: "Doug Steele" <dbdoug at gmail.com>
Sent: Thursday, June 17, 2010 9:48 AM
>
> 1. It was Access all right.
> 2. I never carry date values in text fields.  I never set a format in the
> field definition, either.
> 3. The problem was corrected by making sure everyone's computer had the 
> same
> regional short date setting.
>
> A vague memory comes to me that I read somewhere that the SQL processing
> engine in Access always assumes that dates are in mm/dd/yy format, and has
> caused me trouble.  Was that possibly involved?
>