Date format changing itself in query

[Help.Chris]

Hi All,

I have an SQL statement that is generated at runtime in a form for running in a report -

"SELECT [TrainingTasks].[TrainingDate] FROM TrainingTasks WHERE [TrainingTasks].[TrainingDate] >= #01/05/2003# AND [TrainingTasks].[TrainingDate] <= #24/05/2003#"

When this is run is it selected dates outside the selected period, ie 01/04/2003. I have copied the SQL statement across to the query window, and it shows correctly there in SQL view, but when you switch to Design view, the date comparison changes to >=#05/01/2003# and <=#24/05/2003#

ie. first date has change from 01/05/2003 to 05/01/2003 but the second hasn't???

I have no idea why it is switching the date round like this, please help????

Thank you!!!!!

Chris

 

[Fornatian]

I belive that SQL always treats dates as Americanised.

The reason the first date is converted and the second not is because there is no such date as the 5th of the 24th Month 2003 but there is a 5th January 2003. Using the format function should correct this: Format(#24/05/2003#,"dd/mm/yyyy").

Could be wrong though?

 

[FoFa]

It's those screwy european date formats......
Just teasing, I think it converts the date format to US date format when it sends it to the engine, and 1/5/2003 can be either May first, or Jan fifth (depends where you are standing, London or New York), but since the other date has 24 in it, it knows that is a day, not a month. Does not seem to be an issue here in the US.