Microsoft KB Archive/132067

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

Article ID: 132067

Article Last Modified on 8/8/2001

-

APPLIES TO


 * Microsoft Access 2.0 Standard Edition

-



This article was previously published under Q132067



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:

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

<pre class="fixed_text">  iif(Year([myDateField]) < 1930, DateAdd("yyyy", 100, _ [myDateField]), [myDateField])

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

Keywords: kbinfo kbusage KB132067

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.