MS Access and dates - does anyone know how it REALLY works?!

[John_G]

Hi All -



I've been doing a little experimenting (A2010) to see how Access handles dates in queries and code, and my conclusion is that if Microsoft has been trying to confuse the world, they have succeeded beyond their wildest dreams.

Here's what I did. I have a little database for golf scores, with a PlayDate field, type Date. Three of the records have a value of Oct 5, 2010.

Now, common wisdom (not mine, apparently) says when you use dates in query criteria and use the '#' delimiter, the date has to be in mm-dd-yy format, i.e. month first. So, Oct. 5, 2010 is 10-5-2010.

I wote a simple little query to show two fields, and put this in the date critera: 10-5-2010.

Access automatically changed it to #10/05/2010#, which is fine.

I run the query and - no records! ???

Now I look at the SQL for the query :

SELECT [Golf Scores].PlayDate, [Golf Scores].CourseCode
FROM [Golf Scores]
WHERE ((([Golf Scores].PlayDate)=#5/10/2010#));

Access has reversed the month/day in the date!!! Small wonder it didn't work.

I don't know what others out there think, but I call that a MAJOR bug. I am in Canada, and our system date format is set to dd-mm-yyy, and I wonder if Access is being just a little too clever for its own good (and ours) and is somehow using that setting in the query.

Just to add to the confusion, in the immediate window (VBA more or less), a DCount:

?dcount("playdate","[Golf Scores]","playdate=#10-5-2010#")

DOES require the mm-dd-yy format - the above gives 3 as expected.

Can anyone shed some light on this - is it a bug, or some obscure "undocumented feature"?

Thanks All!

John

[June7]

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

[orange]

John,

I had similar issue --still not completely understood -- but check you settings in control panel. It's probably set , like mine, to use/display dd/mm/yyyy.
It seems to allow you to enter in "Canadian format", but records in Access as US??? If I use Format (someDate,"m/dd/yyyy") it worked for single digit data and month and displayed the leading 0 for month as expected???
Allen Browne says M$oft tried to be real smart, but only leads to confusion - to which I agree.

[John_G]

Orange -

What MS does and worse what they ASSUME defies logic. In the user interface - forms, query criteria, - where users enter dates, MS assumes the format is the system setting, even if the date is enclosed in # tags, as it might be in query criteria. I have always been led to believe that any date between # marks had to be MDY (regardless of system setting), but no. Only sometimes.

You'd think MS could enable users to set the date format that Access uses everywhere, including SQL and VBA, regardless of the system date format setting.

I wonder how many non-USA users have been caught by this, without realizing it?

[orange]

John_G,

I asked some others about Access Dates and your comments.

One very experienced developer responded with:

There is no "sometimes". In the GUI, the date format is always localized except if you specify another format in the Format property.
In VBA and SQL, date string expressions are always read in US, then local, then ISO format until a match.
For CDate and DateValue, however, the sequence is local, US, ISO.
For ADO and FindFirst, only the ISO format is reliable.

Also:
The workaround in SQL code is always to use the ISO format: yyyy-mm-dd.
It will never fail.

[orange]

Original response:
There is no "sometimes". In the GUI, the date format is always localized except if you specify another format in the Format property.
In VBA and SQL, date string expressions are always read in US, then local, then ISO format until a match.
For CDate and DateValue, however, the sequence is local, US, ISO.
For ADO and FindFirst, only the ISO format is reliable.

Also:
The workaround in SQL code is always to use the ISO format: yyyy-mm-dd.
It will never fail.


More info re Dates:

The difference between DateValue (and TimeValue) and CDate is that for a date/time expression, CDate will return the full date/time value while DateValue and TimeValue only returns a part of it.

In addition you have CVDate which - though labelled obsolete - works perfectly well and has the advantage over CDate that it accepts Null values (returning Null).

The great value of DateValue is that it reads a textbox using the local format as the first attempt. Thus a date like 2014-09-11 will here by default be displayed as 11-09-2014, and DateValue will read and convert this to the date/time value of 2014-09-11.

This is where much code fails. If you have a query with filtering on a textbox this way:

Select * From Table Where DateField = [Forms]![frmMyForm]![txtDate]

it will work if you specify [Forms]![frmMyForm]![txtDate] as a parameter of data type Date (and the form is open).

But if you write SQL code like this:

strSQL = "Select * From Table Where DateField = #" & Me!txtDate & "#"

it will fail outside the US as it will result in:

Select * From Table Where DateField = #11-09-2014#

To solve this, the simple method shown by Stuart will work, though you should make it a habit to use the ISO sequence:

strDate = Format(Me!txtDate, "yyyy\/mm\/dd")
strSQL = "Select * From Table Where DateField = #" & strDate & "#"

or

strSQL = "Select * From Table Where DateField = DateValue(" & Me!txtDate & ")"

You will often meet this faulty construction which only shows a lack of understanding of the mechanics:

strSQL = "Select * From Table Where DateField = #" & CDate(Me!txtDate) & "#"

The reason it fails is, that even though CDate correctly converts the expression to a date/time value, this cannot be concatenated with the SQL string, thus Access has to cast it to a text expression. However, this will be done as to the local settings resulting (here) in 11-09-2014 where you would need 09-11-2014.

Further

Actually DateValue() and CDate() are different. Cdate will convert anything that looks like a date (such as a string) to a Date type value. DateValue will also convert strings to dates, but its real purpose is to return *JUST* the date. So if the data you pass to it includes a time stamp, you get back just the date, as in...

? datevalue("8/22/2014 9:50:30 AM")
8/22/2014