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

• Microsoft Access version 2.0

SUMMARY

This article describes two methods that you can use on a field formatted for the Short Date data type so that it displays 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 to display fields formatted for the Short Date data type 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 49, inclusive. Therefore, these methods work accurately only for dates between the years 1950 and 2049, 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 1950 (that is, the last two digits are 00 through 49), it adds 100 years to the date. To use this method, follow these steps:

1. 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
   
   

2. 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
   
   

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

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

4. Open the form in Form view, and enter the following sample data in the MyShorDate text box:

         12/25/01
         10/20/50
         9/8/95
         7/16/05
         8/15/49
         1/1/00
   

   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 1949. 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, 2049
         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 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]) < 1950, DateAdd("yyyy", 100, [myDateField]), _
      [myDateField])