Navigation:  Articles > Jul-1998 >

Taming the Century Beast

Previous pageReturn to chapter overviewNext page

Stu Alderman          
Access has both strong algorithms and marvelous tools for handling dates. Even Access 2.0 can be made Y2K compliant, safely and innocuously (though there's at least one fiendish little bug).
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.
A year 2000 strategy
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.
A timely review
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)



Print CVDate(2958465)


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.
Making decisions
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.
Playing with your date
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.
Table 1. The control values for sdaAdjustCentury.


Returned value


Adjusted within a range of 99 years ago to the present year as defined by Now().


Adjusted within a range of -49 years before to 50 years after the present year as defined by Now().


Adjusted within a range having the current year to 99 years after the present year as defined by Now().

The function sdaAdjustCentury is analytical in its approach and therefore lends itself to use in systems using all kinds of time and date formats. It also adjusts dates based on the current value of the system clock, so it can be used almost indefinitely as the years progress. The code might be a little hard to follow, so unless you think you'll enjoy the exercise of analyzing it, you might want to consider it a black box. The routine appears in Listing 1.

Listing 1. The century adjustment routine.


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)


       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)


       sdaCenturyAdj = DateAdd("yyyy", _

           intYrsToOffset, varDate)

     End If

   Case Else

     sdaCenturyAdj = varDate

     MsgBox "Invalid Adjustment Range", 16, _

         "Camarillo Technology Associates"

 End Select



 On Error GoTo 0

 Exit Function



 Select Case Err

   Case Else

     MsgBox Err & "> " & Error$, 16, _


 End Select

 Resume sdaCenturyAdj_Exit


End Function

In the accompanying Download file, there's a sample database containing the sdaCenturyAdj. I've also put in the form frmDates (as shown in Figure 1) to demonstrate how the function works. The form frmDatesExpanded is also included in the download file. It has additional controls to allow you to experiment with turning grooming off and viewing the data behind time components.

199807_sa1 Figure 1
The field for entering your sample data (txtDate) is set up with a Format property of "Short Date" and an Input Mask of "99/99/00;0;_". The value entered into the field is adjusted using sdaCenturyAdj via its On Exit event. However, even before the On Exit event occurs, Access will attempt to validate the data according to the values assigned to its various properties.
Changing the rules
In Access 2.0, "Short Date" will be validated by assuming that it's a date falling in the range 1/1/1900 to 12/31/1999. Access 7.0 and 95 will evaluate "Short Date" according to the rules in Oleaut32.dll. The version of Oleaut32.dll that comes with Access 7.0 uses the same rules as Access 2.0, but the version that comes with Access 97 interprets years from 00 to 29 as being 2000 to 2029 (you can use the new version of Oleaut32.dll with Access 7.0).
Unfortunately, several Microsoft products come with their own versions of Oleaut32.dll, so you can never be sure which version will exist on all target machines. For this reason, if nothing else, the use of sdaCenturyAdj makes sense because it essentially circumvents the effects of Oleaut32.dll.
The function sdaCenturyAdj works very well, except for that little bug that I mentioned at the beginning of this article. As I said, Access 2.0 and Access 7.0 will attempt to interpret a "Short Date" as following in the range of 1/1/1900 to 12/31/1999. Because of the Gregorian calendar, 2/29/2000 is a valid date and 2/29/1900 isn't. When the older versions of Access try to convert 2/29/00, they fail because they assume that the year is 1900, which isn't a leap year.
To handle this problem, I added code to circumvent "Short Date" and to handle 2/29/00 as an exception. Because I generally want dates displayed using "Short Date", I use some VBA code to remove the format in txtDate's On Enter event:

Me!txtDate.Format = ""

This eliminates the validation problem with 2/29/00 but lets the On Exit event accept values that might not be valid dates. As a result, the On Exit procedure in the form doesn't call sdaCenturyAdj -- it calls the sdaGroomDate function. After all the necessary processing, the On Exit routine resets the Format propery of the txtDate control to cause the groomed date to be displayed properly:

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


     txtLongDate = txtDate

     txtNumber = CDbl(txtDate)

   End If

 End If


 Me!txtDate.Format = "Short Date"


End Sub

The sdaGroomDate is responsible for handling the 2/29/00 function and for assuring that the entry can be converted into a valid date. It will issue an error message and return a Null if there's a problem. If there aren't any problems, sdaGroomDate calls sdaCenturyAdj:

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#


     MsgBox "Invalid Date - Please Try Again"

     Exit Function

   End If

 End If


 sdaGroomDate = sdaCenturyAdj(varDateIn, intFlag)


End Function

The sdaGroomDate function is a requirement for Access 2.0 and for Access 7.0 versions using the older Oleaut32.dll. It isn't needed for stock versions of Access 97. Since it's impossible to predict which version of Oleaut32.dll will be in use on all systems, it would probably make sense to use sdaGroomDate for all your date entries.
Successful implementation of a Y2K-compliant application in Access is mostly a matter of ensuring that all date entry is properly handled and correct years are assigned. The old industry acronym GIGO (Garbage In, Garbage Out) can't find a better example than this.
Before fielding an application, a comprehensive test plan and execution is a must. Beyond normal incremental testing, you might want to consider tests involving:

Y2K entry and processing with a 19XX system date.
Y2K entry and processing with a Y2K system date.
19XX entry and processing with a Y2K system date.

Factoring the various versions of Access, international date formats, and the Oleaut32.dll considerations, your testing grid might soon become unmanageable. The only suggestion I can make is to try to eliminate as many variables up front as possible. You might also want to limit testing to certain representative functions and use those as templates throughout your code. Hopefully, the tools discussed in this article will help you write better applications while reducing your load as a developer.
Download CENTURY.ZIP on this page
Stu Alderman is a principal of Camarillo Technology Associates, a consulting and system development organization headquartered in Reno, NV. 702-747-2892,
Sidebar: The Gatesian Calendar?
Aside from accommodating the change of century, some programs also don't handle the year 2000 correctly in calculating leap years. Most of us believe that every fourth year is a leap year, but that's not always true. Since the time of Julius Caesar, astronomers realized that our year wasn't exactly 365 days long. By adding an extra day every four years, the calendar was brought into close alignment with the actual year. The calendar where every fourth year is a leap year is known as the Julian calendar.
By the late 1500s, it was noted that the calendar year and the actual year were getting progressively out of kilter. In 1582, Pope Gregory XIII instituted the Gregorian calendar. It uses the same rules as the Julian calendar except that century years are leap years only if they're evenly divisible by 400. Therefore, the year 2000 is a leap year, while 1900 isn't.
The Gregorian calendar is almost perfect, but it still accumulates an error of approximately three days every 10,000 years. Perhaps when Microsoft develops an algorithm to skip an extra leap year every 3,333 years, the Gatesian calendar will be born. Unfortunately for Bill, it would be unreasonable to institute this new calendar until at least the year 3248.
Sidebar: Living Through a Y2K Audit
Mark Davis
At my company, upper management hired a firm to come in and make sure that we were Y2K compliant. That was just fine with me, until I found out they wanted to test all of my Access applications! I tried to fight this. My argument was that since Access is Y2K compliant, all of my applications would be also. After all, my apps were nothing more than Access databases with VBA code, so what's the problem? Management, however, insisted that all my applications be tested.
My biggest concern was that the Y2K team would take a huge chunk of my time as I explained to these "outside programmers" how my applications worked. I imagined that they'd then spend many hours testing these applications (with my help, of course) and finally issue a "clean bill of health." I could issue that bill of health for a lot less time and money!
And what if the Y2K team did find a problem with one of my applications? My situation is probably somewhat unique, since the applications I write are for our internal department use only. As a result, I'm the one to both catch and fix any Y2K problems. Let's say that one of my apps parses a date somewhere and assumes the date is in the format xx/xx/xx. If the application breaks, I have to debug and fix the error. If a programmer from the Y2K team finds this problem . . . I have to debug and fix the error.
So what happened, anyway?
The Y2K team did their testing, and to their credit they didn't take much of my time at all. They simply asked for copies of the applications and went on their merry way. From what I could gather, they did things like run searches on the database to find all the date datatypes and then tested those areas specifically. Also to the credit of the Y2K team, it didn't take them long to test the applications. The end result was a "clean bill of health!". (Surprise!)
According to the programmer I talked to, the only problems he'd come across with Access 95 (so far) were running out of "real estate" on reports and forms that needed to display a four-digit year. Even though I was against it at first, it's kind of nice to have outside confirmation that my applications are Y2K compliant, especially since it didn't take much of my time (and I didn't have to pay for it!).