By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
448,844 Members | 1,472 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 448,844 IT Pros & Developers. It's quick & easy.

date format apparently changing mid-program

P: 9
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
Expand|Select|Wrap|Line Numbers
  1. 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!

Luganville, Vanuatu
Jul 28 '08 #1
Share this Question
Share on Google+
3 Replies

Expert 2.5K+
P: 3,532
Allen Browne has an excellent article on this problem. Hopefully it'll help you with yours!

Linq ;0)>
Jul 28 '08 #2

P: 9
Thank you so much - that was really helpful!
Jul 29 '08 #3

Expert Mod 15k+
P: 31,662
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.
Aug 5 '08 #4

Post your reply

Sign in to post your reply or Sign up for a free account.