Date Format change created by SQL Statement?

[El Rebelde]

I need to select multiple records from a listbox which is designed to look like a datasheet. I will use the selected records to select records with matching dates in another recordset.

A sample selection results in the following SQL statement, as seen the immediate window:

SELECT * FROM WorkRecord WHERE WorkRecord.Recorddate IN(#29/11/2013#,#02/12/13#,#03/12/2013#);

These are UK dates (29th November, 2nd and 3rd December) My problem is that this is interpreted by Access as:

SELECT *
FROM WorkRecord
WHERE (((WorkRecord.Recorddate) In (#11/29/2013#,#2/12/2013#,#3/12/2013#)));

So, only the records from November are correct.

Can anyone suggest the best way to ensure consistency, please?

 

[Brianwarnock]

You must use US date format for dates in SQL.

Dates after 12th aren't changed as they will not be dates, if you follow me.

Brian

 

[El Rebelde]

Thanks Brian

I simply created another column of 0cm width in my list box to hold a calculated field "Format([Recorddate],"mm/dd/yy").

That did the trick!

Stuart

 

[Galaxiom]

I simply created another column of 0cm width in my list box to hold a calculated field "Format([Recorddate],"mm/dd/yy").

That sounds like working around an existing problem by introducing another instead of actually dealing with the cause.