Problems with dates... 13/01/2000 does not equal 01/13/2000

[seismoguy]

I am just getting started programming in Access 97 and now need your help.

I want users to enter dates in a form using the MM/DD/YYYY format, but stored in the database as YYYYMMDD. I also want to make sure these dates are valid dates...even if they bypass the form and enter the dates straight into the table.

So, I created a table with a date/Time field, using these parameters:

Format : yyyymmdd
Input Mask : 00/00/0000;0;_

This seems to do the job, but when I enter the date 13/01/2000 and leave that field, I see 20000113 instead of 20001301 (and displays the date 1/13/00 when I re-enter that field). Can anyone give me suggestions?

 

[RickSpr]

The Format property of the table column doesn't specify the internal format of the date--you can't control that, nor do you need to with a date/time field. What the Format property controls is how it's displayed. Since you specified format "yyyymmdd", that's what is being displayed.

The internal stored form of a date/time field isn't even mmddyyyy or ddmmyyyy. It happens to be more like a Single, where the integer part of it is the number of days since December 30, 1899. Access takes care of converting it for you. (The fractional part is a fractional day, and is used to hold the time of day. Since you're not using time of day, the fractional part will be 0.)

Don't worry about your users keying in invalid dates in the table. Since you're using a date/time field, Access won't let them get away with it.

So all you have to do is change your Format property in the table. (If you've already created a form, you may have to change it there, too. Access only copies the Format from the table when you first add the field to a form.) Rick Sprague

 

[cjLiteStep]

also note with access when you enter a date such as 1 13 00 instead of 13 01 00, because there is no 13th month, access automatically enters it as jan 13
so sometimes it will autocorrect an input error, leaving you with an incorrect date