Date format is being wierd - Day Month interchanging 1

[Ranvier]

Hi,

I have a strange problem. I am running an sql query from vba code within Microsoft Access. I am passing the value of the date from a combo box into the sql string.
My date value (varDate) is 4/5/2008 (4th May 2008)

this is passed into the sql string as ...#" & varDate & "#....

when i debug.print the sql string to the immediate window it appears fine 4/5/2008 (4th May 2008) but it doesnt pull the data back that i expect (there are records present for the 4th May 2008.
The strange part is when I copy my sql string that is printed out in immediate window and put it in a query builder (using sql mode) and then switch the query to design mode the date appears in the criteria box as #5/4/2008# (5th April 2008) which doesnt have any records which is why my data isnt being retrieved via the vba sql string.

Running Access 2003 format from 2007 on XP. Also running Access on 2003 on XP on another PC and acts the same.

Anyone come across this problem before?

Thanks

 

[lameid]

If you go into control panel and select regional and language options do you see today's date as you expect to see it formatted? It sounds like your system is set for English (United States) instead of an appropriate country.

In Europe (and possibly the rest of the world), dates are written d/m/y where as in the United states it is m/d/y. I'm in the States so I have not seen the issue but I have read about it. Based on my quick test I think this will solve it for you. Although I have read about some indiosynchocies based on the regional version of Office used. Unfortunately I don't remember anything specific on versions. Almost everything is tied to the Windows region setting.

 

[Golom]

Note that Access used mm/dd/yyyy as it's default format for any date that is ambiguous regardless of your locale settings. For example

05/03/2008 on input will be translated as May 3, 2008 and not March 5, 2008

To get around this use an unambiguous format such as 2008-03-05
(i.e. yyyy-mm-dd).

Dates coming out of the system do observe the locale settings.

 

[Ranvier]

I checked the regional settings on the PC and they are set to (our) standard UK date format dd/mm/yyyy. My user has just got a new(ish) PC and transferd his database and this is when the problem started so I can only assume it is some regional setting (as the database/code has not changed) so i'm not sure what made the difference there.


I formatted the date in the sql string to:

...#" & format(varDate,dd-mmm-yyyy) & "#....

and it is now retrieving the data back properly. Although i can use this as a workaround it does mean i have to make amendments in numerous places where i use the date variable. I'm still a bit baffled as to why it happened in the first place.

BTW as a note i'm putting my format string as:

format(varDate,dd-mmm-yyyy)

from memory as i'm not at work anymore and can't remember what exactly i put down but it's something like that - anyway it seemed to do the trick

but still baffled

 

[missinglinq]

Actually, I believe I recently read somewhere that Access SQL always assumes dates to be mm/dd/yyyy regardless of the Regional Settings on the machine, which would explain why formatting the date in your SQL worked.

The Missinglinq

Richmond, Virginia

There's ALWAYS more than one way to skin a cat!

 

[TheAceMan1]

How are ya Ranvier . . .

In sync with [blue]missinglinq[/blue], have look here: Return Dates in US #mm/dd/yyyy# format

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[Ranvier]

Thanks for all that folks!