ACC2: Displaying Years 2000 and Later for Short Date Data Type

ID: Q132067

The information in this article applies to:

Microsoft Access 2.0

SUMMARY

This article describes two methods that you can use on a field formatted with the Date data type so that dates can be entered in a year later than 1999.

MORE INFORMATION

Unless you explicitly enter the year in a date field as 2000 or later, Microsoft Access saves the year portion as 1900 - 1999 as appropriate. For example, if a field has the Date/Time data type, and you type the date as 01/01/00, Microsoft Access saves the year portion internally as 1900. If you enter the date as 01/01/2000, Microsoft Access saves the year portion internally as 2000. However, the date still appears in the date field as 01/01/00 if displayed using the Short Date format

There are two methods you can use enter dates formatted using the Short Date format with years later than 1999; however, both methods have limitations in that they add 100 years to the input date only if the last two digits in the date are 00 through 29, inclusive. Therefore, these methods work accurately only for dates between the years 1930 and 2029, inclusive.

Method 1

Method 1 adds a procedure to the field's AfterUpdate event that checks the year, and if the year is less than 1930 (that is, the last two digits are 00 through 29), it adds 100 years to the date. To use this method, follow these steps:

Create a new, blank form as follows and save the form as Test1:


      Form: Test1
      -------------------------------
      Text Box:
         Name: MyLongDate
         ControlSource: =[MyShortDate]
         Format: Long Date
      Text Box:
         Name: MyShortDate
         Format: Short Date
         AfterUpdate: Event Procedure

Click the Build button to the right of the AfterUpdate property to open the Module window. Note that the following two lines appear in the Module window:
```
Sub MyShortDate_AfterUpdate ()

End Sub 
```

In the blank space between the two lines above, type the following code:


  If Year(Me!MyShortDate) < 1930 Then
     Me!MyShortDate = DateAdd("yyyy", 100, Me!MyShortDate)
  End If

Open the form in Form view, and enter the following sample data in the MyShortDate text box:

Note that when you enter a date in the MyShortDate text box, the AfterUpdate event procedure adds 100 years to the date you enter if the year is between 1900 and 1929. The corrected date is displayed in long date format in the MyLongDate text box as follows:


      Date You Entered in    Date Displayed in
      MyShortDate Text Box   MyLongDate text box
      -------------------------------------------------
      12/25/01               Tuesday, December 25, 2001
      10/20/50               Friday, October 20, 1950
      9/8/95                 Friday, September 08, 1995
      7/16/05                Saturday, July 16, 2005
      8/15/49                Sunday, August 15, 1949
      1/1/00                 Saturday, January 1, 2000

Method 2

Method 2 uses the expression below in the Update To line in an update query. You can use this method if you have a table that already contains dates that you want to convert to the year 2000 and later. The expression assumes that the name of the field you want to update is myDateField. Substitute the name of the field in your table for the myDateField field in the expression below.

NOTE: In the following expression, an underscore (_) at the end of a line is used as a line-continuation character. Remove the underscore from the end of the line when re-creating this expression.


   iif(Year([myDateField]) < 1930, DateAdd("yyyy", 100, _
      [myDateField]), [myDateField])

REFERENCES

For more information about how Microsoft products are affected by year 2000 (Y2K) issues, please see the following Microsoft World Wide Web site:

http://www.microsoft.com/y2k/

Additional query words: Y2K Year 2000 millennium two-thousand two thousand

Keywords : kbusage kb2000 TblFldp
Version : WINDOWS:2.0
Platform : WINDOWS
Issue type : kbinfo