Date Formatting - Again!

[gray]

Hi

Access 2007, MDB file, Local Machine

Just when you think you've got your head around this.....

I am in the U.K. so I want dates to be displayed in forms and tables with the local format of dd/mm/yyyy. When I call Now() I do indeed get the correct format returned e.g.

01/04/2012 13:36:43 (April !st 2012)

Because of the laregly unpredictable and potentially horrific results with dates (I would hate to get my tax return wrong!!), I have got into the habit of adding all dates into my tables with # delimiters:-

Dim Rec_Date As Date
 
Rec_Date = NOW()
SQL_STR = "UPDATE My_Table SET Date_Rec_Added = # " & Rec_Date & "#"
Execute... blah blah

This (I think) adds the date in the required U.S. format of mm/dd/yyyy .

In my VB modules, I also try to convert any dates submitted by U.K. users into U.S. format and then always # them for table retrievals / calulations / comparisons etc.

Now, I had expected Access/Windows to display dates in my forms and tables with the local U.K. format... it/they don't. I sort of thought that was their job (using locale settings)? I do not have any formatting set on textboxes or table column defs.

What doesn't this happen please?

 

[sparks80]

Hi,

I am a fellow UK citizen with endless headaches using dates in VBA.

VBA will assume that a date in the format ## / ## / #### is mm/dd/yyyy regardless of the regional settings in windows. Even if you supply the date using the # symbol it will still reverse the order of the day and month, which I agree is very frustrating.

One solution is to convert the date into an unambiguous format - such as dd-mmm-yyyy for example. Access will always interpret 06-APR-2012 correctly, whereas 06/04/2012 can be altered.

See if this minor alteration to your code helps with the data entry:

Dim Rec_Date As Date
 
Rec_Date = NOW()
SQL_STR = "UPDATE My_Table SET Date_Rec_Added = # " & Format(Rec_Date,"dd-mmm-yyyy") & "#"
Execute... blah blah

When you display the record on a form you just need to specify the correct date format - this should follow the regional settings specified in the control panel.

 

[missinglinq]

One of the most respected Access gurus in this or any universe is a guy named Allen Browne, and it just so happens that he lives in Perth, Australia, and has an excellent article on this very subject:

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

Linq ;0)>

 

[gray]

Thanks Gents...

I'd read Allen's excellent article.... plus many others but confused myself even further...

At one time I was adding dates into [date formatted] columns using quotes... so...

Date_Rec_Added='" & Now() & "'" ..

this displays nicely in local format in forms and tables (which surprised me!) but, of course, causes all sorts of headaches when calcuating / retrieving etc.. So I then moved to trapping all dates input, converting them to U.S. , then '#'ing them (probably over the top).... doing calcs in U.S. then converting them back....

I've given your technique a whirl Sparks80 and indeed they do display properly thenks.

Just wondering what the effect is now of using code such as:-

SELECT Blah blah WHERE Date_Rec_Added BETWEEN (#" & NOW() & "#) + 10 AND #12/12/2012#

I guess it still does all calcs / retrieves based on the U.S. format but then they are nicely displayed in U.K. form?

Thanks

 

[missinglinq]

...Just wondering what the effect is now of using code such as:-

SELECT Blah blah WHERE Date_Rec_Added BETWEEN (#" & NOW() & "#) + 10 AND #12/12/2012#

Have no idea, being on the opposite side of The Pond, from you, but using your example of

BETWEEN (#" & NOW() & "#) + 10 AND #12/12/2012#

is not likely to prove it one way or the other, since a date of

12/12/2012

cannot be misinterpreted by Access! You have to use something like

13/12/2012

to test it out, as that can only be December 13, 2012!

Also, while

Now + 10

will work in most situations, because Access is sometimes lenient in its handling of Dates (because of legacy questions) you really should use the DateAdd() function for this kind of thing.

DateAdd("d", 10,NOW())

Linq ;0)>

 

[gray]

Geez... this really is a minefield and a half....

Sorry, mine was a poor example. I guess a better question is if I enter dates as per Spark80's

Rec_Date = NOW()
SQL_STR = "UPDATE My_Table SET Date_Rec_Added = # " & Format(Rec_Date,"dd-mmm-yyyy") & "#"
Execute... blah blah

And then searrch for

BETWEEN #4/5/2012# AND #7/8/2012#

Would one expect to get results that are between

a) the 4th of May 2012 AND the 7th of August 2012
or
b) the 5th of April 2012 AND the 8th of July 2012.... yikes!

I guess I need to go and try to put these in and try.... I really hate blind-spots like this... they can come to haunt you months/years later.

P.S. Thanks for the tip on DateAdd...!

Thanks

 

[sparks80]

It's even more of a minefield than you might at first think too.

If you enter the date 06/04/2012 as dd/mm/yyyy into VBA then it will reverse the day and month to give 04/06/2012.

However if you enter the date 13/12/2012 it cannot correspond to a 13th month, so VBA will assume you mean 13th December.

So in one case it will reverse the order of day and month, and in the other it won't.


To avoid confusion I would think of the data entry and data display as separate entities. The code I gave above, or following Allen Browne's superb advice should allow you to enter the data into the table correctly.

If you are going to use a SELECT query in VBA to then display the data then you will have to employ a similar technique to ensure the dates are interpreted correctly.

Either use Allen's SQLDate function or something like this for every date in the criteria for the query:

Format([Date_Rec], "dd-mmm-yyyy")


PS. On a side issue if you are ever importing data from a CSV file then use an import specification - or the same problem of reversed days and months occurs.

 

[RainLover]

FYI

ALL Dates and Time are stored as Numbers regardless of Format.

00.00 = 30/12/1899 12:00 AM

 

[gray]

Hi

Thanks for all the input...

Been experimenting and I'm curious to understand why the two techniques below render different results... Method 1, where the date is formatted 'in-line', is the one I'm going to use as it renders dates in UK format. Method 2, where I format the date first, renders results in US format?

Method 1 (as per Spark80)

Dim Add_Date As Date
 
Add_Date = Now()
 
SQL_Str = "UPDATE My_Table SET Date_Record_Added = #" & Format(Add_Date, "dd-mmm-yyyy hh:nn:ss") & "# WHERE blah blah"
CurrentDb.Execute SQL_Str etc

Method 2

Dim Add_Date As Date
 
Add_Date = Now()
Add_Date = Format(Add_Date, "dd-mmm-yyyy hh:nn:ss")
 
SQL_Str = "UPDATE My_Table SET Date_Record_Added = #" & Add_Date & "# WHERE blah blah"
CurrentDb.Execute SQL_Str etc

Incidentally, I read one theory that said to always use the 2012-12-31 format as this will never be misinterpretted by Access... it will always be 31st Dec 2012 whether on US or UK machine.

LSD could not be more mind-bending

Thanks

 

[smig]

in SQL strings always use the US format - month/day/year
In any other place you can use any other format.
when I compare days I use:
Fomat(myDate1, "yyyymmdd") = Fomat(myDate2, "yyyymmdd")

 

[gray]

Hi Smig

Thanks for the reply... I think, though, that if you always use #[US format]# in the SQL strings, when setting a date it will appear in the tables and forms in the US (mm-dd-yyyy) format??

rgds

 

[smig]

I mean when you create query string that should update
As I said when I compare I format as yyyymmdd

In tables and forms format as you wish

 

[RainLover]

Gray

Look at my post # 8

 

[sparks80]

Hi,

Don't worry when you write the SQL code how it will appear in the table. Rainlover is right - the date/time is really just a number relating to the number of days since 1/1/1900.

You have to specify the date/time in the query so carefully to ensure that it is stored correctly. But despite the fact that the data was entered as say "mm/dd/yyyy" does not mean that it will be displayed like that when you open a table/query/form.

The way the data is shown depends on your regional setting, and the properties of the object or control.

 

[sparks80]

gray,

to answer your other question:

Dim DateAdd as Date

' This will store the current date and time in a variable called DateAdd
' VBA stores the date as mm/dd/yyyy, but since you have reversed the 
' day and month, these *may* now be the wrong way round, depending on
' whether the reversed digits are valid or not
' You really only need DateAdd = Now()
DateAdd = format(Now(),"dd-mmm-yyyy")

' Now when you use the variable in SQL code it will use the American date format
' It is here that you need to format the date to ensure it is used correctly
strSQL = "UPDATE ..."

Long story short, format the string when you write the SQL statement, not when you store the date in a variable.

 

[gray]

Phew! Thanks for your forebearance!

Yes, I think I have been conceptualising this incorrectly. I have hit my head with a saucepan and now understand that, by using:-

Format(Add_Date, "dd-mmm-yyyy hh:nn:ss")

"mmm" converts the month to 'names' of "Jun","Apr","Feb" etc and this assists the DB Engine by removing doubt as to which chars are month and which are day (before it goes off and converts these to it's own internal format anyway).

If we don't do that it automatically assumes the U.S. form where chars 1 & 2, are month, chars 3 & 4 are day..... (notwithstanding all the wierd idiosyncrasies mentioned above)

By George I think he's got it...... !

Thanks to all for the assistance...

 

[sparks80]

If I had a pound for every time I've had to hit my head with a saucepan I'd have very few brain cells and a very sore head.

Glad to be of service!

 

[smig]

Glad we could help

 

[RainLover]

Rainlover is right - the date/time is really just a number relating to the number of days since 1/1/1900.

A minor point but the Date is 30/12/1899.

Some interesting Information

01.00 is 12:00 AM on 31st Dec 1899
00.50 is 12 Noon on the 30th Dec 1899
00.00 is 12 AM on the 30th Dec 1899
- 00.50 is 12 Noon on the 30th Dec 1899
- 01.00 is 12 AM on the 29th Dec 1899
- 01.50 is 12 Noon on the 29th Dec 1899

These numbers are what is saved. There is NO consideration to the Format and/or Region.

Numbers are converted to Dates in the various Formats for the end user to visualise in a manner that they would expect to see.

 

[sparks80]

Useful to know, thanks Rain