Argh - Dates keep flipping to US format 2

[MrMode]

i am populating and filtering a list via vba OnLoad of a form

I was using Access 2013 but had to move to a machine running Access 2007. The database is now running in Access 2007 and has 'suddenly' started flipping dates around and I do not understand how to stop that from happening.

SELECT * FROM tblTest WHERE tblTest.ActivationDate <#" & me.text9 & #"

Debug.Print me.list0.Rowsource shows the date correctly in the sql as dd/mm/yyyy, but as soon as it is pasted into a query, the date is flipped around to mm/dd/yyyy

I have tried to format the date

SELECT * FROM tblTest WHERE tblTest.ActivationDate <#" & Format(me.text9, "dd/mm/yyyy") & "#"

But again, even though it is OK when debug.print it is flipped around when pasted into a query.

How do I force the date to remain formatted the way I want it and execute in the query correctly or do I have to stick with formatting the date to US and then executing it?

Also, is this something to do with moving from 2013 - 2007 and a setting that I have missed?

 

[SkipVought]

Hi,

You must use an UNAMBIGUOUS date structure if you want to convert a date string to a Date Value: yyyy/mm/dd.

personally I think it's better to have the user select from a Date Picker or use separate fields to select year, month, day.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[MajP]

Access and JET expect all date literal in the American Format.

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

 

[Darrylles]

UK here.

I have a table with 2 date fields: StartDate, EndDate.
StartDate is set by default in the table as Now().
On the form, when a user creates a new record; this automatically defaults the Startdate to dd/mm/yyyy format as required.

When the user wants to 'close' the record - I have code set to populate the EndDate so:

	EndDate = Now()

Guess what? It stores THIS date as mm/dd/yyyy!

The only way to 'fix' this is by using this code:

	EndDate = Format(Now(), "mm/dd/yyyy")

Which stores it as dd/mm/yyyy (correctly).

Now I understand the need for UNAMBIGUITY, but do not understand the need for INCONSISTENCY.

ATB,

Darrylle

Never argue with an idiot, he'll bring you down to his level - then beat you with experience.

 

[SkipVought]

Please post your own question in your own new thread.

BTW Dates are just NUMBERS like 47543. That DateSerial balues can be formatted to DISPLAY a date in any format you choose: d/m/yyyy, m/d/yyyy or any other format you might invent.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[MajP]

Guess what? It stores THIS date as mm/dd/yyyy!

No it does not. No date is stored in dd/mm/yyyy or mm/dd/yyyy format. As Skip said all dates are stored as number with an integer and decimal part. What you see in a query, form, or datasheet is simply a formatted representation of that number. You can make it show in almost unlimited formats. The integer part is the number of days since 31 dec 1899 the base date. The decimal part is the fraction of a day since midnight which can be formatted as the time.
So 1/1/1900 12:00pm is the number 1.5

 

[Darrylles]

SkipVought,

Can I ask you where the question is in my post?

It was of course a statement - not a question (for the post's information).

Maj,
I know exactly how dates are 'stored' (I am a 50 year old, qualified software engineer), I don't (and shouldn't need to) care HOW it physically stores it - I DO care that it stores it as I expect it to.
But ok, let's be pedantic; maybe it doesn't 'store' it as xx/xx/xxxx, but it certainly STORES it in a way that ensures that it DISPLAYS it in the table (with IDENTICAL field definition types) - differently, when using EXACTLY the same function to acquire the date.
This sort-of implies that the database engine uses a 'different' Now() function to the Now() function that VBA uses.

I don't have access to the offending code (at home), but will post it Monday.

(EDIT The different methods (using EXACTLY the same function) of storing exactly the same date stores a COMPLETELY DIFFERENT number to the db! Thus, it DISPLAYS a completely different date. Now Maj - please give me your expert opinion as to why that is

ATB,

Darrylle

Never argue with an idiot, he'll bring you down to his level - then beat you with experience.

 

[Darrylles]

MrMode,

I've done some experimenting at home (Access 2007) as opposed to at work (Access 2003 - where my problem above occurs).

Ignoring the superfluous detail of how it stores dates as numbers - check how the dates 'display' in your table. Are they displaying 'incorrectly' e.g. mm/dd/yyyy?

I have found the following (in Access 2007):

    strSQL = "INSERT INTO tblDates " & _
         "           (end_date) " & _
         "VALUES     ('" & Now() & "')"     'dd/mm/yyyy

'displays' in the table as 'dd/mm/yyyy', but,

    strSQL = "INSERT INTO tblDates " & _
         "           (end_date) " & _
         "VALUES     (#" & Now() & "#)"     'mm/dd/yyyy

'displays' in the table as 'mm/dd/yyyy'. UPDATE works in the same way.
And, when you SELECT these fields - they display in exactly the same way that they are stored (Yep, they are STORED differently - a COMPLETELY different numeric value is STORED to the db).

What is ambiguous, is that the INSERT statement allows both single quotes AND hash symbol to INSERT a date, but the SELECT statement with single quotes around a date throws an error (and insists on the hash).
The INSERT has to be quotes and the SELECT has to be hashes (if you want dd/mm/yyyy format) consistently.
Now, you'd expect that if you unambiguously stick to those formats, all would be fine. However, I do suspect that Microsoft has even been 'switching' these across releases - thus your 'problem'.
The problem does not seem to be coding ambiguity, but with inconsistency of the tool.
I do not want to have to format every value as I display it, I want to STORE it as I wish it to be displayed (wherever possible) - solve the problem in one, single place - at source - not every time it's accessed in future!

So, is your problem possibly due to how you originally inserted the date (albeit correctly - for that version of Access)?

Try both INSERT formats in both Access 2007 and 2013 - I'll bet that what they 'allow' has changed.
I'll check 2003 - 2007 (Monday).

The way to amend HOW the current dates are stored is:

strSQL = "UPDATE tblDates " & _
         "SET    end_date = format(end_date,'dd/mm/yyyy');"
or:

strSQL = "UPDATE tblDates " & _
         "SET    end_date = format(end_date,'mm/dd/yyyy');"

depending on how it is actually storing the date (and yes - it will STORE it differently, and it will STORE 'dd/mm/yyyy/ as 'mm/dd/yyyy' and vice-versa (for the guru 'experts' - it stores a COMPLETELY DIFFERENT number)).

In conclusion: I believe that UNAMBIGUOUS coding is very important, but it only comes a close second to coding CONSISTENTLY, and it seems that with MS Access - that has been removed from our control (from version to version).

And, a question for the forum: how on earth can this be coded in a way that ensures data consistency across MS Access versions? Is it even possible?
The only 'solution' that I can think of is a 'wrapper' function that performs ALL record inserts, checks the Access version, and amends the SQL insert statement for dates.

ATB,

Darrylle

Never argue with an idiot, he'll bring you down to his level - then beat you with experience.

 

[SkipVought]

Yep, they are STORED differently - a COMPLETELY different numeric value is STORED to the db

Sure! June 12 and December 6: d/m vs m/d, results in a different Date Serial Value.

Here's what I guess happened:
[tt]
EndDate = Now() assigned something like this to EndDate: "12/6/2015 09:07:44"
EndDate = Format(Now(), "dd/mm/yyyy") assigned this to EndDate: "6/12/2015"
[/tt]
You must also realize that providing or returning a string, as the Format() function does, and assigning the string to a variable that has a Date data type, forces a string-to-DateSerial conversion and your VB Editor graciously forgives your error of assigning a string to a Date, and performs a cover conversion for your benefit, which does not happen in the SQL compiler. Hence it "throws an error (and insists on the hash)"

The answer to using dates sucessfully is to use the UNAMBIGUOUS yyyy/mm/dd string structure prior to conversion via # delimiters.

@Darrylle, sorry I assumed that you were posting a separate "question." It was not apparent to me on first reading, that your comments were intended to bring enlightenment to this discussion.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[MajP]

the different methods (using EXACTLY the same function) of storing exactly the same date stores a COMPLETELY DIFFERENT number to the db! Thus, it DISPLAYS a completely different date. Now Maj - please give me your expert opinion as to why that is wink

First of all I posted the original solution is that you always have to use unambigous formatting. So I always use the below function for any date in a sql string.

{code]
Function SQLDate(varDate As Variant) As String
'Purpose: Return a delimited string in the date format used natively by JET SQL.
'Argument: A date/time value.
'Note: Returns just the date format if the argument has no time component,
' or a date/time format if it does.
'Author: Allen Browne. allen@allenbrowne.com, June 2006.
If IsDate(varDate) Then
If DateValue(varDate) = varDate Then
SQLDate = Format$(varDate, "\#mm\/dd\/yyyy\#")
Else
SQLDate = Format$(varDate, "\#mm\/dd\/yyyy hh\:nn\:ss\#")
End If
End If
End Function
[/code]

Maybe it is that your terminology is wrong, but you are just wrong when saying it changing the way the date is stored. All dates are stored the exact same way. The problem is that you are storing the wrong thing. Reread the link I posted.

If you pass a literal date the JET engine assumes americand format. So if you pass #6/12/2015#, then you passed 12 june, you did not pass 6 December. Does not matter what regional settings you have. It is not storing it wrong, you are passing the wrong date.

So this is wrong

strSQL = "UPDATE tblDates " & _
"SET end_date = format(end_date,'dd/mm/yyyy');"
or:

strSQL = "UPDATE tblDates " & _
"SET end_date = format(end_date,'mm/dd/yyyy');"

If end_date is actually a dateserial then you cannot do the first. Because if the date is 6 December you will insert june 12th into the table. Again not a problem with how the date is stored, it is the wrong date being stored.