Taming the Century Beast

Stu Alderman

Computers are a recent invention. For most of their history, memory and storage have been a very large part of their cost. Because time is a seemingly infinite continuum, a concession was made to limit the amount of data used to describe both times and dates. Currently, the most famous concessions to memory limits are the two-digit years in older COBOL systems. These programs are the heart of the Year 2000 (Y2K) problem that's making the news these days. The sidebar, "The Gatesian Calendar?" describes the history of our calendar and points out a wrinkle in leap-year calculations that gave me some problems.

Even some of today's PCs will suffer problems moving into the year 2000. Some publications have already reported user-reported problems when system clocks cross the New Year's boundary on 12/31/99. These problems range from software license timeouts to unexplained system software and hardware failures. If you're in an environment where this could be a problem, a bit of careful testing in this area might be a good idea before you even begin to look at your Access databases.

Access developers have been largely shielded from many of the problems facing other developers regarding Y2K. Our computers are newer, our ROM BIOS services are more recent, and we're not terribly worried about storage volumes. Best of all, Access has built-in capabilities to handle any date a business application might need for the next several thousand years.

Several times, I've been asked to certify that my applications are Y2K compliant. On one occasion, I had to refuse because the certification document indicated that all date entry and display must use four-digit years. To the credit of the bank requesting certification, when I explained my Y2K strategy, they agreed that it seemed an excellent way to go. Mark Davis' sidebar, "Living Through a Y2K Audit," describes his experience with a Y2K compliancy audit.

My year 2000 strategy is to assure that all dates entered into my applications are verified to be assigned the proper year in the correct century. I also made it a rule that, unless it's impossible to do otherwise, all online date entry would use the format MM/DD/YY.

While occasional date entries using a four-digit year might be tolerable, many users find it an irritation because it's hard for them to retrain themselves out of their old habits. They have a case. For high-volume data entry, a four-digit year isn't only an irritation, it significantly impacts productivity. My solution was to design a module that would automatically adjust two-digit year data to represent the proper century every time a date is entered.

In order to make sure that you're familiar with the tools in my Y2K toolkit, I'm going to review some of the material covered in Angela J. R. Jones' article, "Saving Time with Date/Time Functions" from the June 1998 issue of Smart Access. This will also give me an opportunity to point out an interesting bug that Angela didn't mention.

If you've ever tinkered with the data in a Date/Time field or a variant containing Date/Time data, you might have discovered that it contains a floating point number. The Date/Time field contains what Access calls a Variant of VarType 7. This is a floating point number spanning a range of -657,434 to 2,958,465. In this format, 1 is equal to one day, and any fractional part of the number represents time. Therefore, 0.25 equals six hours, and 7 equals a week.

To store a date, Access will assign a number that's equal to the number of days the date is offset from December 30, 1899. Using the preceding numbers, you can calculate the range of dates Access accepts using the CVDate function in the immediate window:

Print CVDate(-657434)
1/1/100

Print CVDate(2958465)
12/31/9999

CVDate accepts a string or a number and tries to convert the data to a Variant of VarType 7. If you pass CVDate a value containing a fractional component, it will return a Variant containing a date and a time:

Print CVDate(35950.8525)

6/4/98 8:27:36 PM

If your application needs to handle dates where the time component isn't a factor, you should be aware of some possible pitfalls. Suppose you use the Now() function to put a time stamp on your data when customers return tapes to your video store. If you then try to sort your data by time and then title, it might appear that the titles aren't sorted at all! That's because your query might be sorting by date/time, while your report is only showing the date. As a result, your report puts "Zorro" before "Batman" because "Zorro" was returned earlier in the day.

Another pitfall is trying to directly determine equality between two variants containing dates and times. If you use the equal (=) operator, even though each variant contains the same date, you'll come up with False because they probably don't hold the same time.

Access provides a number of functions to help you combat these problems:

• DateValue(stringexpression) -- This function returns a Variant of VarType 7 containing a date value but no time component. This function will also accept a Variant of VarType 7 in place of stringexpression, so it's invaluable for grooming your data to remove time components.
  
  
• DateDiff(interval, date1, date2[, firstweekday][, firstweek]) -- The DateDiff function can be used for comparisons, as well as for calculating intervals of date and time between two Date/Time values. It effectively removes considerations of time when comparing dates by allowing you to specify that you want to work in periods of weeks, months, or years. DateDiff is an extremely versatile function that will become second nature if you need to handle a lot of date/time processing. (See Angela's article in the June issue for more detail.)
  
  
• DateAdd(interval, number, date) -- This function is used for adding or subtracting various intervals of date or time from a Date/Time value. Like DateDiff, it will ignore the Date/Time components that aren't part of the interval it's operating upon.

CVDate, DateValue, DateDiff, and DateAdd comprise a fully featured set of utilities that will handle the bulk of your analytical processing. They work dependably across most dates in the range of the Date/Time field. The only place I've noted any discrepancies using these tools concerns operations on or around the date 12/30/1899. For instance, the expression DateDiff("d", #1/1/1899#, #1/1/1900#) will evaluate to 364. I suspect this has something to do with how Microsoft decided to evaluate the difference between negative and positive values. Since the chance of this type of evaluation occurring in my users' business applications is so remote, I've decided not to worry about it.

The Format function is the only other function I use on a very regular basis. With it, I can control exactly how dates and times are displayed.

When you're planning your application, it's a good idea to define when you want to store dates, when you want time, and when you want both. If you only want dates, you should groom all input using DateValue, or you can control processing using a combination of other functions. Using both approaches is like wearing a belt plus suspenders: It might prevent worry and catch a few oversights, but it's not very efficient.

Using an input mask when accepting keyboard data is another method for accepting a date-only value. In my example, I use the default input mask for Short Date as supplied by the Input Mask Wizard, 99/99/00;0;_. This mask allows you to use spaces as place holders for the first four digits and requires numeric characters for the last two. It works well for both MM/DD/YY and DD/MM/YY date entries.

For displaying dates in data entry fields, I recommend the "Short Date" setting rather than a user-defined setting such as "MM/DD/YY". This allows users, via the International section of the Windows Control Panel, some control as to how dates are normally entered and displayed. When using "Short Date", Access first evaluates the date based on its internal rules, then displays the date based on the setting in the Control Panel.

Now that I've defined the objectives and tools, it's time to go into the implementation of Y2K-compliant processing. In addition to making careful use of the tools built into Access, I initially implemented my strategy using just one procedure -- sdaAdjustCentury. This function accepts a Date/Time variable, varDate, and adjusts the century component to one of three ranges based on the value of intRange, as shown in Table 1.

Function sdaCenturyAdj (varDate As Variant, _
 intRange As Integer) As Variant
'* Returns date based on varDate's year Mod 100 
'  adjusted to the following ranges:
'  intRange = -1     -99 and   0 years of now
'  intRange =  0     -49 and +50 years of now
'  intRange =  1       0 and +99 years of now
On Error GoTo sdaCenturyAdj_Err

  Dim intYrsFromNow As Integer
  Dim intOffsetXX As Integer
  Dim intYrsToOffset As Integer

  If Not IsDate(varDate) Then Exit Function
  
  '* Diff in years from Now to varDate
  intYrsFromNow = DateDiff("yyyy", Now, varDate)
 '* Last 2 Digits of Diff
  intOffsetXX = intYrsFromNow Mod 100             

  '* Calculate years from varDate to Offset-from-now
  intYrsToOffset = intOffsetXX - intYrsFromNow

  Select Case intRange
    Case -1              '* -99 to 0 years from now
      If intOffsetXX > 0 Then

        sdaCenturyAdj = DateAdd("yyyy", _
          intYrsToOffset - 100, varDate)
      Else


        sdaCenturyAdj = DateAdd("yyyy", _
          intYrsToOffset, varDate)
      End If
    Case 0               '* -49 to +50 years from now
      Select Case intOffsetXX
        Case -99 To -50
          sdaCenturyAdj = DateAdd("yyyy", _
            intYrsToOffset + 100, varDate)
        Case -49 To 50
          sdaCenturyAdj = DateAdd("yyyy", _
            intYrsToOffset, varDate)
        Case 51 To 99
          sdaCenturyAdj = DateAdd("yyyy", _
            intYrsToOffset - 100, varDate)
      End Select
    Case 1                '* 0 to +99 years from now
      If intOffsetXX < 0 Then
        sdaCenturyAdj = DateAdd("yyyy", _
            intYrsToOffset + 100, varDate)
      Else
        sdaCenturyAdj = DateAdd("yyyy", _
            intYrsToOffset, varDate)
      End If
    Case Else
      sdaCenturyAdj = varDate
      MsgBox "Invalid Adjustment Range", 16, _
          "Camarillo Technology Associates"
  End Select

sdaCenturyAdj_Exit:
  On Error GoTo 0
  Exit Function

sdaCenturyAdj_Err:
  Select Case Err
    Case Else
      MsgBox Err & "> " & Error$, 16, _
          "sdaCenturyAdj/basDates"
  End Select
  Resume sdaCenturyAdj_Exit

End Function

Me!txtDate.Format = ""

Sub txtDate_Exit (Cancel As Integer)

  If Len(txtDate & "") > 0 Then
    txtDate = sdaGroomDate(txtDate, CInt(grpAdjust))
    If Not IsDate(txtDate) Then
      Cancel = True
      Exit Sub
    Else
      txtLongDate = txtDate
      txtNumber = CDbl(txtDate)
    End If
  End If

  Me!txtDate.Format = "Short Date"

End Sub

Function sdaGroomDate _ 
   (varDateIn As Variant, intFlag As Integer) As Variant 

  If Not IsDate(varDateIn) Then
    If CStr(varDateIn & "") = "02/29/00" Or _
       CStr(varDateIn & "") = " 2/29/00" Then
      varDateIn = #2/29/2000#
    Else
      MsgBox "Invalid Date - Please Try Again"
      Exit Function
    End If
  End If

  sdaGroomDate = sdaCenturyAdj(varDateIn, intFlag)

End Function