[AccessD] International date format problem

Hi Arthur

But you are mixing two different things here:

1. Parsing a string expression for a date
2. Inserting a date parameter in SQL

Your task here is #1.
Rocky's task was #2.

What happens for Rocky's task is that when concatenating a date value (from the recordset field) this will be casted to a string according to the Windows settings. Of course, if this format differs from what SQL expects, it will fail.

Stuart's method - using DateValue in the SQL string - then converts this back to a date value when the query runs. A bit clumsy, I feel. However, this will always succeed because DateValue's first attempt to read the string is to read it as to the Windows settings.
The other method - using Format - converts the date value to a date string expression understood by SQL using the format you decide. The preferred format is the ISO format, yyyy/mm/dd. Then this string is concatened with the other parts to form the final SQL string. No further conversion or processing will be performed when running the query.

Which method you prefer is a matter of taste, except that method #1 only works in environments where the DateValue function is understood, like Access.

/gustav


>>> fuller.artful at gmail.com 02-08-12 13:38 >>>
Pursuant to this thread, I decided to try a few things:

? DateValue("2102-02-02")
2/2/2102
? DateValue("Jun-21-12")
6/21/2012
? DateValue("21-Jun-12")
6/21/2012
? DateValue("2012/06/02")
6/2/2012
? DateValue("21/Jun/12")
6/21/2012
? DateValue("2012.06.02")
--> Type mismatch

These are the only date formats I could think of at the moment. Conclusion:
DateValue() works almost all the time.
Arthur