The Calendar Application
A Date To Remember, Page 5
Unless you haven't realized it yet its 1996 ! Its just 4 years to the date of 01/01/00(01.01.00 or 01-01-00 or 00-01-01) so you can be sure that some of the applications that you are writing today will still be in use when this fateful date comes round. Now is the time to prepare for the next century !
Unfortunately there are still a few surprises with dates that need addressing before the year 2000. If you are using short dates througout your code then you must replace all references with long date conversions as the expression Format$("01/01/00","Long Date") will return a date of the first of January 1900 and all short dates with a Year component of "00" will return a date of 1900 ! All calculations that rely upon the calculation of the difference between two dates (such as interest calculations) will be incorrect, or worse still the turn of the century will cause your applications to crash.
?DateDiff("d","30.12.99","01.01.00") -36522 ?DateDiff("d","30.12.99",format$("01.01.00","Long Date")) -36522Implications:
|Long Date||1 January 1900||1 January 2000|
From the above table you can see that to avoid future problems when storing dates you must choose either a general or Long Date format and an input mask that requires the year to be entered as 4 digits. The Medium and Short date formats always convert the years "00" and "2000" to "00".
For MaskEdit controls a complete solution is to place a validation routine in the controls LostFocus event to determine if the date entered is a date with a four digit Year and if not convert it.
Sub mskDate_LostFocus(C As MaskEdBox) If Len(C) = 0 Then Exit Sub ' no date entered Dim yr As Integer If IsDate(C) Then yr = Year(C) If yr < 1930 And yr > 1899 Then C = Format$(Left(Format$(C, "c"), 6) & 100 + Year(C), C.Format) End If Else MsgBox "Not a valid date format" C.SetFocus End If End Sub( Place the above subroutine in the maskedit controls' LostFocus event. Note that this routine is hardwired to convert year entries of "00" to "29" to "2000" to "2029". Alternatively you can set the controls mask property to accept only 4 character years.)
The format property of a MaskEdBox control determines the format that the date value entered into the control will be displayed. Users can enter a date in any format that they choose but it is automatically fomatted to the controls format property when the control looses focus. Because the valid date format for an application can be altered by selecting a different country in the international settings in Win95 it is not a good idea to hardwire a date format mask for a maskedbox control as your edit box will have to be replaced for each country that you distribute your software to.
TextBox controls are treated similarly. A validation routine is placed in the controls LostFocus event that converts any digit year dates to a 4 digit year to avoid any ambiguity between the years "00" and "2000". This is the only way to ensure that the years 1900 and 2000 are not confused when entering a date in a TextBox field as TextBoxes do not have a mask property with which to validated the entered date.
Sub txtDate_LostFocus(C As Control) If Len(C) = 0 Then Exit Sub ' no date entered Dim yr As Integer If IsDate(C) Then yr = Year(C) If yr < 1930 And yr > 1899 Then C = Format$(Left(Format$(C, "c"), 6) & 100 + Year(C), "c") Else C = Format$(C, "c") End If Else MsgBox "Not a valid date format" C.SetFocus End If End Sub(Place the above subroutine in the TextBox's LostFocus event. Note that the above routine will format the validated date to the general date format.) You can paste the above subroutine directly into a MS Access Module and use it directly to validate an MS Access Text Box that holds a date value.
Make sure when you are saving a table as a delimited text file to specify 4 digit years in the Import/Export setup options.
1. The sample data in text form when the 4 digit years option is not set :
"DateFormatId","GeneralDate","LongDate","MediumDate","ShortDate" 1,1.1.00 0:00:00,1.1.00 0:00:00,1.1.00 0:00:00,1.1.00 0:00:00 2,1.1.00 0:00:00,1.1.00 0:00:00,1.1.00 0:00:00,1.1.00 0:00:002. The same date in text form when the 4 digit years option is set :
"DateFormatId","GeneralDate","LongDate","MediumDate","ShortDate" 1,1.1.1900 0:00:00,1.1.1900 0:00:00,1.1.1900 0:00:00,1.1.1900 0:00:00 2,1.1.2000 0:00:00,1.1.2000 0:00:00,1.1.2000 0:00:00,1.1.2000 0:00:00Conclusions:
Users should be educated Now to force them to always enter a 4 digit year date.
As MaskEdBox controls require an additional VBX/OCX control to be distributed with a shipped application their use for date fields should be avoided. A TextBox control can provide the required validation functionality.
Access Table date fields should have the format property set to "General Date" to avoid developer and user confusions. Additionally Acces Form fields that contain dates also should be validated to ensure that all date entries contain a 4 digit year part. The subroutine txtDate_LostFocus can be copied directly into an Access module without any further modification and be used to validate and format access date fields as well.
When exporting a table containing date columns ensure that the export options have been set to 4 digit years.