Interpretation of ambiguous dates by Microsoft Access textbox

3
votes

I've been searching around without any luck for an MSDN or any other official specification which describes how 2 digit years are interpreted in a date format textbox. That is, when data is manually entered into a textbox on a form, with the format sent to short date. (My current locale defines dates as yyyy/MM/dd)

A few random observations (conversion from entered date)

29/12/31 --> 2029/12/31
30/1/1   --> 1930/01/01

So far it makes sense, the range for 2 digit dates is 1930 to 2029. Then as we go on,

1/2/32  --> 1932/01/02 (interpreted as M/d/yy)
15/2/28 --> 2015/02/28 (interpreted as yy/M/dd)
15/2/29 --> 2029/02/15 (interpreted as M/d/yy)
2/28/16 --> 2016/02/28 (interpreted as M/dd/yy)
2/29/15 --> 2029/02/15 (interpreted as M/yy/dd)

It tries to twist about invalid dates so that they are valid in some format, but seem to ignore the system locale setting for dates. Only the ones that are invalid in any format (like 0/0/1) seem to generate an error. Is this behavior documented somewhere?

(I only want to refer the end user to this documentation, I have no problem with the actual behavior)

1
formsvbadatems-access
Access always tries to interpret based on the locale settings, and JET uses, #MM/DD/YYYY# settings. - PaulFrancis
Any idea what it does after trying to interpret based on locale settings and failing? A link to some documentation would also be appreciated... - Indivara
Yes, official documentation from Microsoft on this issue is extremely scarce. I suspect that, like some other Microsoft implementations (most notably the RTF document "spec") the behaviour is the documentation, or "it is what it is". Perhaps we can come up with a reasonably definitive answer here and then this question can serve as the "unofficial documentation". BTW, I cannot recreate your results for 2/29/15: my copy of Access 2010 rejects it as invalid and I've never seen an application interpret a date as month-year-day. - Gord Thompson
@PaulFrancis Good comment because the distinction between the interpretations of (1) the ACE/Jet database engine, and (2) the MS Access UI (and VBA functions like CDate()) is important. However, ACE/Jet also recognizes #yyyy-mm-dd# date literals and interprets them correctly. - Gord Thompson
@GordThompson, thank you. I have never experimented with yyyy-mm-dd. I will have a play around. I normally parse it to mm/dd/yyyy or convert them to Decimal, just to be sure. - PaulFrancis

1 Answers

0
votes

The 29/30 split was settled this way with Access 2.0 as of 1999-12-17 in the Acc2Date.exe Readme File as part of the last Y2K update:

Introduction

The Acc2Date.exe file contains three updated files that modify the way Microsoft Access 2.0 interprets two-digit years. By default, Access 2.0 interprets all dates that are entered by the user or imported from a text file to fall within the 1900s. After you apply the updated files, Access 2.0 will treat two-digit dates that are imported from text in the following manner:

00 to 29 - resolve to the years 2000 to 2029 30 to 99 - resolve to the years 1930 to 1999

Years that are entered into object property sheets, the query design grid, or expressions in Access modules will be interpreted based on a 100-year sliding date window as defined in the Win.ini on the computer that is running Access 2.0.

The Acc2Date.exe file contains the following files:

 File name      Version     Description   
 ---------------------------------------------------------------------    
 MSABC200.DLL   2.03        The Updated Access Basic file
 MSAJT200.DLL   2.50.2825   The Updated Access Jet Engine Library file
 MSAJU200.DLL   2.50.2819   The Updated Access Jet Utilities file
 Readme.txt     n/a         This readme file

For more information about the specific issues solved by this update, see the following articles in the Microsoft Knowledge Base:

Article ID: Q75455    
Title     : ACC2: Years between 00 and 29 Are Interpreted as 1900 to 1929

That article can be found here as KB75455 (delayed page load):

ACC2: Years Between 00 and 29 Are Interpreted as 1900 to 1929

As for the 2/29/15 is not accepted here where system default is dd-mm-yyyy, so there are limits to how much creativity Access/VBA puts into interpreting date expressions.