date format apparently changing mid-program

[inglesp]

Hi everyone

I've come across some perculiar behaviour in a little database app I'm making with Access.

I have a form for users to create a filter for data that goes into a cross-tab query. One of the filters is by date - users can choose a start month and an end month. From this, I construct a string - something like "between #1/7/2008# and #31/7/2008#" - which I then use as a where condition in the sql statement needed to generate the cross-tab query.

I have a query called "crosstab filtered", and I change the sql for this with

CurrentDb.QueryDefs("crosstab filtered").SQL = strSQL

I can then use this query as the recordsource for my report, etc etc etc.

The problem is that when I inspect my query, the date format for the first date has switched. Instead of, for example, "between #1/7/2008# and #31/7/2008#", I have "between #7/1/2008# and #31/7/2008#".

I have checked in the control panel, and date format is the European format - day/month/year - which is what I want. I have also checked how Access displays dates - Now() also gives the date in European format.

I have no idea where my probelm is, so any ideas would be most useful!

Cheers,
Peter
Luganville, Vanuatu

[missinglinq]

Allen Browne has an excellent article on this problem. Hopefully it'll help you with yours!

http://allenbrowne.com/ser-36.html

Linq ;0)>

[inglesp]

Thank you so much - that was really helpful!

[NeoPa]

What that doesn't explain clearly is how the SQL specific standard interferes with this too (from what you say this is particularly relevant to your situation). See Literal DateTimes and Their Delimiters (#) for a fuller explanation.