VBA Date/Time format setting 5

[aaronjonmartin]

Hi I live in the UK and im trying to do a query involving returning records between 2 dates, i was having problems however it turns out this is due to VBA in access assuming the time format is mm/dd/yyyy not dd/mm/yyyy is there a way i can change this?

Any help with this would be great

Thanks

Aaron

"It's so much easier to suggest solutions when you don't know too much about the problem."
Malcolm Forbes (1919 - 1990)

 

[RoyVidar]

Nope - the Jet engine need dates in a US recognizable format, so when concatinating VBA sql strings, doing find operations etc against the Jet engine, you need to format, for instance:

[tt]...somedate=#" & format$(date,"mm/dd/yyyy") & "#"[/tt]

or use the format "yyyy-mm-dd". Every other place you encounter dates, they will be according to your locale

Roy-Vidar

 

[SkipVought]

Aaron,

First, if you are dealing with REAL DATES, the format sould not matter, calculation wise, since REAL DATES are just NUMBERS, like TODAY is 38245.

Once your calculations are accomplished, then FORMAT the result ANY WAY YOU LIKE using

Format([MyDate], "dd/mm/yyyy")


Skip,
[sub]
[red]Be advised:[/red] When transmitting sheet music...
If it ain't baroque, don't fax it! [/sub]

 

[RickSpr]

VB and VBA always use the English-US locale. That can't be changed, I'm sorry to tell you.

Part of your problem may also be that the data was entered and stored wrong in the first place--that is, that some of your data is erroneous already. Here's a sample scenario: A UK user is entering dates into a table on a PC which has regional settings set for USA date formats. He enters two dates: 22/6/04 and 3/7/04. Because USA date formats are selected, Access initially attempts to interpret the first date as mm/dd/yy, but since 22 is out of range, Access reinterprets it properly and stores it as June 22 2004. With the second date, however, Access makes sense of it as mm/dd/yy, so it stores it as March 7 2004 when what was intended was July 3 2004. Thus, some dates have been stored incorrectly and are wrong.

If this has happened to you, you need to correct the data first, or you will become hopelessly confused trying to fix your VBA code. Check dates which have both month and day <= 12 against outside information--the source documents, if available.


Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[aaronjonmartin]

Thanks for all the posts guys i appreciate it.

Skip,

Im interested in using Format([MyDate], "dd/mm/yyyy"). However i have my date in an asp variable, how can i use format in asp with the date to be formatted coming from a variable?

&quot;It's so much easier to suggest solutions when you don't know too much about the problem.&quot;
Malcolm Forbes (1919 - 1990)

 

[SkipVought]

Alas!

Day([MyDate]) & "/" & Month([Mydate]) & "/" & Year([Mydate])

Skip,
[sub]
[red]Be advised:[/red] When transmitting sheet music...
If it ain't baroque, don't fax it! [/sub]

 

[cuok]

RoyVidar , U R REALY REALY INSTRUCTOR!!
YOUR POST WAS VERY VALUEABL TO ME, THANKS
CUOK