Date/Time stamp in Table design accepts 3 digit year

[fneily]

One of my students pointed this out to me and I just tested it. In Access 2000 and 2002, if you set up a date/time field in table design, switch to datasheet view and enter a date such as 4/23/993, it accepts it as is. If you enter 4/23/003 it changes it to 4/23/2003. Any "year" 991, 992, etc. remains the same. This then messes up any pickoff criteria. Does anyone know why Access accepts such an invalid date? It can't think it's the year 987 because it changes 006 to 2006.
Thanks.

 

[FACETS]

I haven't looked into that but you might want to set the input mask so that a 3 digit date can't be entered at all

 

[Golom]

Just a wild guess but, as I'm sure you're aware, Access wants to help you ... really, really, desparately wants to. So much so that it casts strings or numbers to dates if they are used in a context where a date is required.

In the first case, that includes trying to coerce a string in the form " value1/value2/value3" into something it can interpret as a date. I suspect that what's happening is that Access is saying:

* My internal date format is m/d/yy so I need to get 4/23/003 into that form.
* "4" looks OK as a month and "23" looks OK as a day.
* the year must be "003". Is that a 4-digit date? No, obviously not.
* OK then does it evaluate to being a value between 0 and 99?
* It does! It evaluates to 3, dropping the leading zeros.
* My rule is that 2-digit years between 00 and 30 are in the 21st century and 31-99 in the twentieth so 3 equates to 3 + 2000 or 2003.
* "991", "992", etc. don't have this problem because they do not evaluate to a number between 0 and 99 ... therefore the WHOLE year must be 991 or 992.

To check this out, test something like "4/23/070". If I'm guessing correctly, that should be interpreted as April 23, 1970 and "4/23/025" should be April 23, 2025.

 

[fneily]

Interesting. It switches over at 030. So 029 is changed to 2029, 030 is changed to 1930. Just like you said Golom.

"My rule is that 2-digit years between 00 and 30 are in the 21st century and 31-99 in the twentieth so 3 equates to 3 + 2000 or 2003." Who came up with that rule and why?

It's also nice to know that I can do inventory from the Dark Ages and not need to worry about date conversions.

Thanks for your explanation.

As Facets suggested, I guess some date validation rules must be set up.

Neil

 

[JPMontreal]

Hi,

It’s your Windows setting. To change it go to:

Start > Settings > Control Panel and then Regional Options.

On the tab “Date”, you could change the two digits that will change to 20XX.



Jean-Paul
Montreal
jp@solutionsvba.com
mtljp2@sympatico.ca

 

[JPMontreal]

Hi,

By the way, Date for Access could go from 01/01/100 to 12/31/9999



Jean-Paul
Montreal
jp@solutionsvba.com
mtljp2@sympatico.ca

 

[Golom]

Just a comment on JPMontreal's comment. You can mess with your regional settings and those will be used on output displays by Access ... HOWEVER, it is a documented "feature" of MS/Access that internally it uses "m/d/y" for date conversions regardless of what you set in the Windows Regional Settings.

There are all kinds of horror stories about how "2/5/03" gets interpreted as "Feb 5 2003" when you really meant (and had regional settings set as) "May 2 2003".

 

[JPMontreal]

Hi,

Yes, you’re right on this one. In Montréal, Canada, we use French and English version of office and Windows, sometime both in the same office, side by side. When you do a back end and a front end, to be access by everyone, queries with date become nightmares.
In English, It is m/d/y, in French, it could be d/m/y or y/m/d. Guest what is that date: 02/05/03 in a query. So we need to use a lot of Format() and verification on date fields inputs. I always display them as yyyy-mmm-dd, the input is according of the regional setting of Windows, this way they could see the month in text and realize if it is the right date.



Jean-Paul
Montreal
jp@solutionsvba.com
mtljp2@sympatico.ca