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"))
  -36522
Implications:
There are no problems storing dates inside access tables as all date/time data types are stored in serial date format. The problem occurs with the way that the date input is formatted before it is converted to a serial date and stored.
Input:01/01/0001/01/2000

General Date1/1/001/1/2000
Long Date1 January 19001 January 2000
Medium Date01/Jan/00 01/Jan/00
Short Date1/1/00 1/1/00

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:00
2. 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:00
Conclusions:
There is no date format available that ensures that dates entered with a 2 digit year format are stored as with the correct Year value. All date fields must be validated to ensure that the date entered is the date that the user sees displayed. Date fields should always be formatted using the General Date format ensuring that either an entry containing a two digit year part is either invalid or that the two digit year is automatically converted to a 4 digit year.

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.




[HOME] [TABLE OF CONTENTS] [SEARCH]