FMS Technical Papers - Access, VB, Office, and the Year 2000

Solving the Year 2000 Problem in Microsoft Desktop Application Programs
Including Microsoft Access, Excel, Word and Visual Basic

According to numerous sources, the Year 2000 Problem (also known as the Millennium Bug) really only exists for large COBOL-based legacy systems. This common view has come to be prevalent because many developers consider modern software like the desktop offerings from Microsoft to be ready for the Year 2000 simply because they are "modern". Nothing could be further from the truth. Desktop applications represent a significant risk in terms of non-compliance with Year 2000 issues. And the sheer number of desktop applications deployed around the world tells us that such applications could comprise a large percentage of Year 2000 problems.

This paper is designed to explain the Year 2000 problem as it relates to the following Microsoft desktop products:

Full descriptions of problem areas are shown, along with a detailed methodology for fixing Year 2000 problems.

Before going any further, it is critically important to understand the fact that Year 2000 risk issues exist in desktop applications to a degree not generally appreciated. This information is important because most IT/Corporate data processing departments are devoting the bulk of Year 2000 budgets to legacy and large systems, while ignoring the desktop issue. The following list debunks the most common myths.

The thinking here is that desktop software such as Microsoft Office and Microsoft Visual Basic are new and modern. This is reinforced by the rapid rate with which new versions of desktop software ship. Using this reasoning, you may assume that your applications will not encounter the Year 2000 problems inherent in large scale COBOL systems. As you will see from the rest of this paper, the "modern" argument is not true.

One common "cure" for the Year 2000 problem is to change the Windows Control Panel Short Date format to mm/dd/yyyy. The thinking here is that all Windows software uses the Control Panel's Short Date format for input and evaluation of dates. Unfortunately, this is simply not true. While many of the built-in date operations in Access, Visual Basic and Excel use the Short Date setting, an equal number do not. And custom code and expressions in the database application are almost guaranteed not to respect the Short Date format. Finally, there is nothing to stop a user on a particular machine from resetting the Short Date format back to two years—after all, it makes data entry easier!

#3: I'm safe because the database engine used by Microsoft Access and Microsoft Visual Basic automatically stores all four digits of the year

If you develop database applications, this can be the most dangerous myth. While the Microsoft Jet Database Engine does store all four digits of the year in Date/Time fields, the bulk of data that gets put into tables only has two digit years supplied. In such a case, the application using the database engine has to guess, or "assume" the century, based on a myriad of rules and special conditions that vary by product version. Similarly, data imports and exports in text format almost always handle century information incorrectly--very few use four digit years.

#4: I don't have to worry about the problem because desktop applications are not mission-critical

You have to define mission-critical here. If the desktop applications in use in your organization aren't mission-critical, then why are they in use? Look at this way: even though you are not using Microsoft Excel to process bank statements, or Microsoft Access to manage air traffic control, the chances are that these tools is used in conjunction with such applications to supply front-end data entry, or data import routines that provide high-level summary reports. If desktop applications can't be trusted to work in the Year 2000, then the true mission critical nature of those applications becomes evident.

Although the basics of the Year 2000 problem are fairly widely known, many developers still don't have a grasp on the fundamental concept of the problem. Simply put, here is the problem:

"Computer programs cannot correctly interpret the century from a date which only has two year digits."

Although this definition shows that the problem is quite simple, fixing it can be difficult, costly, and time consuming. The Year 2000 problem can be further decomposed into two main parts: the storage problem, and the interpretation problem.

The first Year 2000 problem is one of storage. On many legacy systems (typically written in COBOL on large systems), application developers worked hard to minimize the storage space required for data. After all, disk storage was prohibitively expensive back in those days. A common practice was to set up date fields with only two digits for the year. Some tightly constrained systems even minimized year storage to one digit—these unlucky developers face Year 2000 issues every ten years!

While this practice may seem short-sighted, remember that in previous years the cost of fixed storage was much higher than in today's market. This, coupled with the traditional developer's goal of making the most efficient system possible, leads to a situation where many legacy systems simply cannot store year data beyond this century. And fixing this part of the problem requires not only restructuring the data set to accommodate four-digit years, but rewriting and re-testing all of the business and program logic associated with those fields.

For most IT/MIS departments, this problem is the most expensive and time-consuming part of solving the Year 2000 problem. Most importantly, the deadline to solve the problem is growing closer and cannot be postponed.

So how does the storage problem affect desktop developers? It depends on the type of data that you are working with. If every database or spreadsheet you work with uses formal date types for fields, the storage issue isn't a problem. Database programs such as Microsoft Access (which shares the Microsoft Jet Database Engine with Microsoft Visual Basic) handle dates correctly. That is, they always store all four digits of the year. So, if your database engine always stores four digit years, what's the problem? In a nutshell, the problem occurs when data comes into your application, either through human data-entry, or by links to external data. This data is inherently in a string format, and there is often only a two digit year.

Think of a mainframe database that stores credit card member information. The database has three date related fields: a membership date, an expiration date, and an anniversary date. Now assume that part of your application needs to bring this data in, massage it, and provide a series of forecasting reports to management.

The first field, called membership date, signifies the date that the customer became a cardholder. The contents of this field equates to a 6 character text field that holds data like "120395". Such a value is supposed to mean December 3^rd, 1995 and "sort of looks like a date".

If the importing program allows you to specify that this field is a date field, it should be able to parse the string text into a formal date field. But the problem here is: what does "95 mean? Does it mean 1995 or 2095? What happens next is based solely on the rule your application's import logic uses.

If the importing program doesn't handle conversion of text fields to dates, you'll end up with a 6 character text field. This is even worse! Because its not readily identifiable as a date field, it can be hard to spot and restructure for Year 2000 compliance.

Now let's look at the second field in the mainframe database. This field stores the member's card expiration date. And anybody who has ever had a credit card knows that that expiration date is expressed as four digits. For example, an expiration of 12/97 means that the card expires in December of 1997.

Now imagine that the mainframe data field that stores this is either a 4-digit number, or a 4-character string. Either way, when you import the data in, you're in deep trouble. What does the "97" mean? Is it 1997 of 2097?

And now we move onto the most insidious issue relating to data storage—the "hidden date" problem. Remember that our third field is called AnnCode. Looking through the data, you see values like:

and so forth. What you probably don't know is that the COBOL programmer who designed the mainframe storage model was running desperately low on disk space when he was asked by the IT manager to add a new date field to hold the card member's anniversary date. Our resourceful programmer realized that he could stuff an entire date into three characters using a clever compression algorithm.

Now you are importing the data into your system on a regular basis. But how do you know what this means? More importantly, when your application goes through a Year 2000 analysis, this mystery field may never be flagged as an issue to address. Why not? Because neither the field name or the data in the field can identify it as date-related.

As you can see, Year 2000 issues can be deeply hidden in a system. Because of this, automated tools can only go so far in identifying the problem. When dealing with data, there is no substitute for good old human know-how.

The second Year 2000 problem is the one that will affect you most as a desktop application developer. It is the problem of interpretation. Almost all modern software that is hosted on the Microsoft Windows platform is designed to hold date data with 4 digits for the year. Development environments such as Microsoft Visual Basic and databases like Microsoft Access all store the full four digits. Therefore, it is very unlikely that you will have to restructure systems to increase date storage size.

The problem then is one of interpretation—even though your applications store four year digits, most will show only 2 digits on data-entry forms, view forms and reports. So if you type in a two digit year, what is actually stored in your application? One would hope that across all development platforms, there would be a clear and consistent rule for this. Unfortunately, this is not the case: the rules for interpretation of short years (those with only 2 digits) depends on a variety of factors. The rules used by each of the products covered in this paper is discussed later.

The MS-DOS operating system understands four digit dates up to the year 2108. Microsoft Windows 3.11 and Microsoft Windows 95 support years up to 2108 also. On the horizon, Microsoft Windows 98 will automatically handle all DOS date century problems. Additionally, Microsoft Windows NT handles BIOS and date century problems automatically as of version 4.0.

The most important of handling the Year 2000 problem for desktop development is to ensure that you understand how your development environment interprets short years. If your application's user types 12/31/97 into your application, what year is actually stored? What about 12/31/00? How do you figure out the rules? The understand this, let's take a quick walk down memory lane.

In the world of 16-bit programming, most applications have their own internal routines for working with dates. For the most part, a two digit year is always assumed to be in the 20^th century. So if you enter a date of 12/31/97, the application stores the value of 12/31/1997. The obvious problem with this approach is that when your application meets the new millennium, a date entered as 12/31/97 defaults to 12/31/1997, not to 12/31/2097.

As work progressed at Microsoft towards 32-bit operating systems and OLE automation, a better solution was obviously needed—why have each application define its own rules about how dates are managed? And was automatic conversion of short years to the 20^th century the best approach? The new solution was to make the rules involving dates to be handled by the operating system and to default to the current century. This solution was developed during the development of Microsoft Visual Basic 4/16 bit and Microsoft Office 95 (including Microsoft Access 95, version 7) and involved two things:

Later, Microsoft decided that defaulting to the current century was not the best approach, so the OLE Automation library was updated to introduce a new rule called the "sliding window": two-digit years between 00 and 29 are interpreted as belong in the 21^st century, all others default to the 20^th century.

So we now have three separate rules governing the interpretation of short years! The problem is further compounded by the fact that even within the same version of an application development tool, you cannot assume that you automatically know which rule will be used. This is because the rule is defined in the OLE Automation libraries for 32-bit operating systems.

For example, Microsoft Access 95 ships with the older OLE Automation library that uses the rule of always defaulting to the current century for two digit years. After Microsoft Access 95 shipped, the OLE Automation library was updated to use the new rule. Just by installing a program that uses the new library, you change how your application interprets years.

The new sliding window rule was introduced into the OLE Automation library that shipped with a multitude of products, including Microsoft Internet Explorer version 3.0 and later, Microsoft Windows NT 3.51 Service Pack 5, Microsoft Windows NT 4.0, Microsoft Windows 95 OSR 2, Microsoft Office 97, Microsoft Visual Basic 5.0 and other products. The bottom line is the OLEAUT32.DLL versions 2.20.4049 and later use the new sliding window technique, while previous versions use the current century rule.

Microsoft Excel on the other hand, does not use the OLE Automation Library in any version for dealing with dates--it has its own rules that depend on the version you are using. Additionally, Excel supports VBA, which does use the OLE Automation Library. You can quickly see how this becomes confusing!

So how do all these rules and libraries affect Microsoft's desktop products? And how do you determine which one is in effect for a given instance of your application? The following table shows how each version of these applications interpret 2-digit years:

Product	Rule	Comments
Microsoft Access, version 2 Microsoft Visual Basic, version 3	All two-digit years are automatically and unchangeably assigned to the 20^th century.	The logic and rules for dealing with dates is built into each program's private core executables and dynamic link libraries.
Microsoft Access 95, version 7	Uses the OLE Automation library.	The rule used depends on which version of the library you have installed.
Microsoft Excel, version 5 Microsoft Excel 95, version 7	A two digit year less than 20 is assigned to the 21^st century. A two digit year greater than or equal to 20 is assigned to the 20^th century.	For example, a value of "12/31/19" is stored as 12/31/2019, where a value of "12/13/20" is stored as 12/13/1920.
Microsoft Excel 97, version 8	A two digit year less than 30 is assigned to the 21^st century. A two digit year greater than or equal to 30 is assigned to the 20^th century.	For example, a value of "12/31/25" is stored as 12/31/2025, where a value of "12/13/30" is stored as 12/13/1930.
Microsoft Visual Basic, version 4, 16-bit	VB 4/16 uses the 16-bit version of the OLE Automation library. This version has never been updated, so the sliding window solution is not available to VB 4/16 applications.	Assumes that all two digit years belong in the current century as defined by the system clock.
Microsoft Access 97, version 8 Microsoft Visual Basic, version, 4, 32-bit Microsoft Visual Basic, version 5	Uses the OLE Automation library.	The rule used depends on which version of the library you have installed.
Microsoft Outlook 97	When dealing with dates in tasks, events, and meeting events, Outlook uses a window that includes 30 year back from the current date and 70 year forward. Birthdays recorded in the Contacts portion of Outlook use a different rule because a greater bias towards the past is needed. In this case, two digit years are assumed to belong in a window between ninety-five years back and five years forward from the current date as defined by the system clock.	You must update your OUTTLIB.DLL file to version 8.03 or events that span the century boundary will not work correctly.
Microsoft Word, version 6 Microsoft Word 95, version 7	The two digit year "00" is interpreted as the year 2000, all other two digit years are interpreted as being in the 20^th century. This rule is used by Microsoft Word when performing a table sort, and when using Date formatting (picture switch) of Quote Fields.	Issues may exist with user-input dates when sorting tables. Additionally, data imported from other programs is represented as text, and should therefore be examined.
Microsoft Word 97, version 8	Two digit years from 00 to 29 are assumed to be in the 21^st century, all others are assumed to be in the 20^th century. A date window of 1930-2029 is used when interpreting two digit year when using the Date formatting (picture switch) of Quote fields.	Issues may exist with user-input dates when sorting tables. Additionally, data imported from other programs is represented as text, and should therefore be examined.

If you are using a product listed above as using the OLE Automation Library, you need to check the version of the OLEAUT32.DLL file on your system to determine which rule is in place. You can check this version manually, or use the supplied code to programmatically check the version.

Open Windows Explorer and go to the Windows\System directory. Locate the file named OLEAUT32.DLL and right-click on it. From the context menu, select Properties. When the Properties dialog appears, select the Version tab and click on 'Product Version' to see the version number for the file. If it is 2.20.4049 or later, the sliding window algorithm is being used. If the version is earlier than this, all two digit years are assumed to be in the current century as defined by the system clock.

You can also check the version of the OLE Automation Library using VBA program code. Place the following VBA code in a new module and run the IsOLELibNewer() procedure to see if the newer version (sliding window behavior) of OLEAUT32.DLL is installed.

Click here to view:
Code Listing 1: Determining Which OLE Library is Installed

If your application is based on a 16-bit program such as Access 2 or Visual Basic 3, or and application that uses one of the older versions of the OLE Automation Library, the sliding window algorithm does not exist. In this case, you have to create your own code-based algorithm. Additionally, even when you are using a version that uses the sliding window algorithm, you must decide if the 00-29 range is right for your application. If not, you need to implement your own routine.

The following code shows a sample sliding window procedure. Note that this version always throws the century information away and supplies the century according the limits set:

So if you can't rely on the default behavior of desktop applications to work in the Year 2000, how do you solve the problem? Although the answer is simple, the implementation of the solution can be complex and time-consuming. This section outlines a strategy for dealing with the Year 2000 problem on a step by step basis.

The General Services Administration (GSA) of the United States government has defined a strict and clear meaning for "Year 2000 Compliance". Since government procurement standards often dictate the design and standards used in software, the GSA rule is a good starting place:

Your definition of Year 2000 Compliance depends entirely on what your application is supposed to do. If your application is mission critical, Year 2000 compliance means that every area of your application that deals with dates has been examined, tested and certified as Year 2000 ready. On the other hand, if your desktop application simply stores personal information that is not used within an organization, or deals with no date data, Year 2000 compliance has a less rigorous meaning. In a nutshell, 100% Year 2000 Compliance means that every aspect of your application will function correctly when year data beyond 1999 is used.

As you go through the certification process, budget and time constraints may force you to address only high-risk issues in your application. The end result of such a process would not be 100% Year 2000 Compliance, but the realities of life sometimes dictate a less than perfect solution.

To ensure that your desktop application is Year 2000 compliant, follow a process that allows you to accurately detect and fix all potential issues. While the exact details of the process you use depend on your budget and application complexity, this section outlines a generic certification strategy that can help.

Before you can identify risks and fix objects, you need a catalog of all objects that make up the application. For example, in Visual Basic applications, include all project source files, including forms, modules, and classes. Access applications typically store all program objects in one or more MDB files, but you still need to consider library databases and ActiveX controls. Within these objects, catalog each of the constituent parts. This information can be kept in a variety of formats, including paper logs. Consider using a database to manage the process, especially for complex projects.

The next step involves examining each object identified in the catalog for possible Year 2000 issues. This can be the most time-consuming step of the entire process because it requires a good understanding of how dates work within your chosen environment, and how your application itself is suppose to work.

In this step, you assign a risk to each object identified as having Year 2000 issues. The granularity of your risk categorization scheme depends on the level of effort you plan for the certification process. In general, three categories suffice:

Once you have defined the risk level of each issue, you must prioritize what needs to be fixed in what order. Common sense dictates that high-risk issues be addressed first, but the dynamics of application-object interaction and multi-member development teams may make such strict coordination impossible. If your goal is 100% compliance, then all risk issues need to be eliminated, so the scheduling by risk becomes less important.

For each issue detected, you need to develop a corrective action. In some cases, this may be as simple as changing the InputMask on an Access form, or changing the format of a date displayed in a spreadsheet. In other cases, major data restructuring or complete overhauls of large amounts of procedural code may be needed.

Once the corrective action has been determined, apply it to the object and test the results. Test by using dates in both the 20^th and 21^st century, and if necessary, with the computer's system clock set in each century.

Once the test has passed, certify that the object is Year 2000 compliant. Do this by updating the catalog your created in the first step to record the fact that object has been examined, fixed, and tested. It is also a good idea to record details about:

This step ensures that corrective actions applied to individual objects do not destabilize the application as a whole. The level of testing required in this step correlates directly with the number of corrective actions that were applied in the previous steps. In other words, if only one issue was detected and fixed in an entire application, the testing carried out in this step could be less rigorous than an application that had 1000 issues detected and fixed.

Once all previous steps have been completed, and no issues have been left uncorrected, and all testing indicates that the corrective actions work, you can certify that your application is Year 2000 compliant.

Note that the use of terms like "compliant" may involve legal issues and guarantees that you are not willing to make. In general, software is never licensed with any type of warranty. If you are working in a commercial or licensed environment, consult the proper legal sources before making any claims about your software.

The system that your application runs on is comprised of a computer and operating system. You must take steps to ensure that there are no Year 2000 issues in this area before starting with the Access application itself.

When the original IBM PC was designed back in 1984, foresight about the next millennium was obviously not in the minds of the engineers. The core problem is that most older personal computers have BIOS systems that won't correctly interpret the change of the century. On these older systems, the real-time clock implemented in CMOS (that battery maintained memory chip that keeps all your BIOS settings, including the date) only has two digits to store the year in.

This problem manifests itself as follows: when the year 2000 rolls around, the clock resets itself to the year 1900. When this happens, the BIOS interprets the year as invalid, and sets the system clock to March 1^st, 1980, which is the first valid date as far as the BIOS is concerned.

Modern personal computers do not have this problem because the BIOS code applies a "windowing" technique that allows the computer to correctly assume the century.

So how do you determine which machines are affected by this "old BIOS" problem? You can easily check your machine because the steps are fairly simple.

Warning: Setting the clock forward on your computer may seem to be a harmless action, but it can have disastrous effects if you aren't aware of all the issues. When setting the clock forward, even for a brief time, be sure to take the following into account:

There are several BIOS checking programs available also. Most are free, and one of the best is called Test2000. You can find this program at: http://www.RighTime.com.

The Windows Control Panel exposes a number of settings under the heading of "Regional Settings". These settings allow you to customize the default date and time formats to be used by your applications. Most well-written Windows applications take these settings into account. Unfortunately they take their value from the Short Date setting, which by default is set to a format that only supports a two digit year. For example, on U.S. versions of Windows, the default is m/d/yy which means that any part of your application that does not have an explicit date format enforced will use the Control Panel default of two digit years.

Because if this, it is commonly believed that you can solve the Year 2000 problem on the desktop simply by setting the Short Date format to include four digit years. Unfortunately, this is not a complete solution for a number of reasons. The first is that most custom routines in applications do not use the short date setting. Look through your application's code and see if you can find a single place where you are actually using routines to check or set the short date format. Chances are, there are no such occurrences—which means that fiddling with the Control Panel settings isn't the solution. Secondly, and perhaps more importantly, there is nothing to stop your application's users from setting or changing the short date format on their own. After all, it is easier to type in two digit years!

As an extra safety net for your application, you may want to have it set the Short Date format at the start of your program. This ensures that any place where Access, VB or Excel use the setting will be correct. If your application does need to read or set the short date format, you can use the following code.

Note that Microsoft's 16-bit code editors, such as those found in Microsoft Access 2, and Microsoft Visual Basic 3 do not support line continuation. Code below that is split with the underscore character ( _ ) should actually be typed on one line.

Click here to view:
Code Listing 4: 16-Bit Code (Access 2 and VB 3) for Getting/Setting the Short Date Format

The following code is the VBA (32-bit) version. Note that line continuation is supported by VBA editors, so you can paste this code into a new module "as-is".

Click here to view:
Code Listing 5: VBA Code for Getting/Setting the Short Date Format

A key problem to address is how your program accepts dates. User interface data entry in most applications does not require the entry of the full four digits of the year. At worst, the application has input masks or custom logic that forces two digit years only. At best, the application probably uses the Control Panel Short Date setting which will require only two-digit years.

Date fields exposed to the user must be formatted to only accept four digit years. This ensures that the operator (with proper training) decides which century to use, not the computer. Similarly, anywhere a date is shown, whether its on a form or report, that date must display all four digits of the year. In places where the entry of all four digits of the year is not practical (high volume, heads-down data entry for example), you must create the logic that supplies the century—you cannot rely on Access, VB or Excel to make the right guess for you.

The first step is identifying any component of your application that allows data entry of dates. This can include datasheets, forms, queries with data entry capabilities, document objects, or spreadsheets. Use the tools available in your application to control date input. Investigate properties such as input masks, validations rules and format properties. Where necessary, write or modify your own validation routines to ensure that century data is being correctly maintained. This step is critically important with 16-bit applications: if your application doesn't allow or require four digit years, the application will absolutely fail in the new century when the user enters a value of 00 for the year 2000. The resulting value (1900) is obviously incorrect.

Equally important is the concept of date display. Any control that displays data should have an explicit Format property setting that shows all four digits of the year. This ensures that the operator can spot check dates and identify anomalies as data is being entered.

A common programming practice (and a poor one at that) is to assign a special "hidden" meaning to dates. For example, the value of 9/9/99 is often used to identify a piece of information that should never be erased, or a date that never occurs, such as the expiration date of a lifetime membership. Be sure to scan your application to ensure that such values are neither accepted, nor acted upon.

In certain cases, Microsoft Access and Microsoft Visual Basic represent date values in a non-Year 2000 compliant way. For example, in Microsoft Access 97 or Microsoft Visual Basic 5, date expressions bounded by the # sign do not interpret the way you may expect. To see this behavior, open Microsoft Access 97, and type the following into the Debug Window. (You can open the debug window at any time by pressing Ctrl-G). Type in the following and press [Enter]:

This looks correct because all four digits of the year are shown, and the assumption rule defined by OLEAUT32.DLL is used.

The real "behind the scenes" value for Microsoft Access 97 is 1930. Note that the full four digits of the year are hidden.

The same problem occurs in the Microsoft Access property sheet on object designs. For example, open a form in design view and click on a control that has a DefaultValue property. In the property sheet's DefaultValue property, type in #12/13/30#. You will see that as soon as you press [Enter], Microsoft Access converts your input value to #12/13/2030#. However, if you type in #12/13/2030#, Access leaves it alone—it shows the century.

While this behavior may seem Year 2000 compliant because the full and correct century is stored, the problem is that for years outside the 00-29 window, the century information is automatically and unchangeably hidden—certainly questionable Year 2000 behavior.

Any application that uses dates is likely to have procedural code that interrogates and manipulates date values. This is often done with string variables, especially in 16-bit programs which lack a dedicated date type in the programming language. Everywhere that such procedural code works with a date is a potential problem. Program logic needs to be examined and tested to ensure that the century part of the date is correctly initialized and maintained.

If you are working with a solution that needs to communicate between desktop applications, you have to examine your logic and procedures to ensure that date data passed between applications is correct. For example, you may have routines that use the Windows Clipboard to pass data between Microsoft Word and Microsoft Access. In such a case, the Copy operation to transfer a date will grab the text representation of the date, and depending on the format of the date, it may miss the century information.

Additionally, you may be using OLE or Dynamic Data Exchange (DDE) to pass data. In either case, century information can be lost from dates, causing Year 2000 problems.

These examples illustrate how inter-application communication can cause problems. Additionally, the simple act of a user using the Copy/Paste commands to move dates from one application to another can be problematic. For example, if a date is displayed in an Microsoft Access form as "12/31/33", when it is copied to the clipboard it is converted to straight text and the century information is lost--an obvious Year 2000 compliance problem.

If your application uses database functionality, there's a good chance you are using Microsoft Access or Data Access Objects (DAO) to use the Microsoft Jet database engine, or an ODBC backend such as Microsoft SQL Server. In these cases, SQL is used to define the operating parameters of queries.

So as the first step, you must identify all SQL strings in your application. The next step is to ensure that the SQL contains no expression code that could cause Year 2000 problems. This is most commonly seen when expressions like Left and Right are used to break a date into its component parts. For example, the following SQL string is not Year 2000 compliant because it removes the century data from a date:

Properties exist in modern development environments to define a specific object's behavior. You can set properties on a variety of objects that may be date related. Most properties allow the input of either an expression or a literal value. For example in an Microsoft Access form control's DefaultValue property, you specify a value to be placed in the control if one is not specified by the user. Two types of values can be placed in such a property:

To ensure that property values are Year 2000 compliant, you must examine each to see if the literal value or expression could cause a non-conformant date to be stored or acted upon.

Custom solutions are often based on the macros you design. Examine all macro routines to detect:

Module code represents one of the more difficult challenges in making your application Year 2000 compliant. Because code can represent dates in so many ways, it can be difficult to find all of the date-related logic in the application. Microsoft products support several variants of module code, all based on the BASIC language:

Most desktop applications contain macro and module code that manipulates data in a procedural fashion. This brings up what could be the costliest Year 2000 issue in terms of identifying and fixing problems.

If procedure code that works on dates is not written with Year 2000 compliance in mind, then its operations should be considered suspect. For example, consider the following code snippet that determines a credit card's expiration date based on the membership date:

This code isn't Year 2000 compliant because it removes century information from existing data before making its calculation. The above code will generate incorrect results.

Identifying non-compliant procedural code is not something that a program can do with 100% reliability. This is because the code may not contain any words, phrases, expressions or numbers that identify it as being date related. For this reason, finding issues with procedural code can take more human intervention time than any of the other problem areas.

The typical approach is to search module code for any word or phrase that identifies the code as date-related. For example, you can search for all language keywords that deal with dates, such as CVDate, DateValue, Year, etc. This technique should identify most of the code that deals with dates. Other code may represent dates but that fact may not be obvious from viewing the code. For example, the following fictitious procedure adds two years to a string date:

This code contains no calls to native date functionality, nor does it use names that identify it as date related. This example shows how difficult it can be to identify, let alone fix, procedural code.

Anytime that a date is represented by a string, you have the potential for problems. This is because the string data type does not "understand" what dates are, or the special rules that govern them. And use of strings for dates in not uncommon in desktop applications. For example, say you get the value of a Date/Time field from a Microsoft Access form and assign it to a string variable. At that point, the string variable is just an assemblage of characters, it has no meaning as a date. Why is this a problem? Because most century errors in date related code happen because they have been converted to strings. Then, the string is decomposed into days, months and years. Subsequent operations on that string or its parts are prone to error unless century issues are handled explicitly by the code.

The issue can be further clarified by thinking in terms of how variants (and Date types in VBA) differ from strings. Date-type Variant or Date variables always store the full four digits of the year. Even when you display such a variable (in the immediate window, or on a form) and only two digits of the year are shown, the full four digits are still there. The century information is merely "hidden" by the application according to the Short Date format defined in the Control Panel.

Access Basic (the language in Access versions 1.0, 1.1 and 2.0) and Visual Basic version 3 do not have a native date data type. It does support date values through the variant data type. Most problems occur when a variant date is used by module code, and the date already has century errors.

For example, assume that code gets a date from a form's control into a variant. Microsoft Access automatically converts the value to date-type variant, but by then, its too late. The century information included in the date is based upon the rule that Microsoft Access used when the user entered the value into the form. So if only two digits of the year are entered, the variant will contain the century dictated by whatever rule is in effect for that version of Microsoft Access or Microsoft Visual Basic. In such a case, the century information contained in the variant must be considered suspect.

If you are using an application that doesn't use a sliding window algorithm, you should write your own. Call this routine any place your application could work with a two-digit year. And remember that even though the newer 32-bit programs such as Microsoft Visual Basic 5, Microsoft Excel 97, and Microsoft Access 97 have a sliding window algorithm, you must determine if the number used for the pivot year is correct for your needs. If not, create and use your own sliding window algorithm. See the sample code earlier in this paper for details.

The year 2000 is a leap year. Additionally, many custom routines to determine leap years are not Year 2000 compliant because they only work with two digits of the year. If your application requires logic to determine leap years, be sure to test your routine to ensure it works in the 20^th and 21^st centuries. This is a Year 2000 problem because many developers still believe (incorrectly) that 2000 is not a leap year. Because of this, many custom leap year algorithms will incorrectly identify 2000 as a non-leap year.

The calculation of leap years is one of the more divisive and misunderstood issues in computer science. Before jumping into the subject, let's take a brief look at why leap years exist at all.

The strict definition of a year is "the interval between two successive passages of the Sun through the vernal equinox." The vernal equinox is the exact moment when the Sun is above the equator moving south to north. This definition of a year is known as the "tropical year" and is 365.24219 days in length ( 365 days, 5 hours, 48 minutes, and 46 seconds.)

With the introduction of the Julian calendar in 46 BC, the number of days in a year was rounded up to 365.25. With the Julian calendar, a simple leap year rule was used: if the year was evenly divisible by four, it was a leap year. Unfortunately, the difference between a tropical year (365.24219 days) and the Julian year (365.25 days) introduced errors that over time added up. This error resulted in a year that was 11 minutes and 14 seconds too long, which adds up to a whole day every 128 years.

By the 1500's, this error added up to approximately 10 days. This problem was addressed in the Gregorian calendar instituted in 1582 by Pope Gregory XIII. The fix was to make the determination of leap years a bit more accurate. In the Gregorian calendar, a year is defined as being 365.2425 days in length. This is not exactly equal to a tropical year, but is so close that the error amounts only to about 3 days in 10,000 years. If this error is a problem for you (you are tracking the decay of atomic particles in your Access application for example), you may need to switch to Coordinated Universal Time (UTC) and employ "leap seconds". The coverage of this is unfortunately beyond the scope of this paper.

Since we now use the Gregorian calendar, we use the rule made official by papal decree: "Every fourth year is a leap year except for century years that are not evenly divisible by 400."

What does this mean to you? First, the Year 2000 is absolutely a leap year! Don't let anybody tell you differently. If they do, they don't understand how to correctly determine a leap year. If you come across such a person, show them the following Latin text of Pope Gregory's definition in the February 24, 1582 document entitled "Inter Gravissimas":

"Deinde, ne in posterum a XII kalendas aprilis aequinoctium recedat, statuimus bissextum quarto quoque anno (uti mos est) continuari debere, praeterquam in centesimis annis; qui, quamvis bissextiles antea semper fuerint, qualem etiam esse volumus annum MDC, post eum tamen qui deinceps consequentur centesimi non omnes bissextiles sint, sed in quadringentis quibusque annis primi quique tres centesimi sine bissexto transigantur, quartus vero quisque centesimus bissextilis sit, ita ut annus MDCC, MDCCC, MDCCCC bissextiles non sint. Anno vero MM, more consueto dies bissextus intercaletur, februario dies XXIX continente, idemque ordo intermittendi intercalandique bissextum diem in quadringentis quibusque annis perpetuo conservetur."

Note that using the formula tells us that 1900 is not a leap year. However, for compatibility with incorrect historical standards in computer software, some programs consider, such as Microsoft Excel, consider 1900 to be a leap year. How you handle this issue is determined by whether you want to be correct or compatible. The following code samples show two different ways to determine leap years.

The first procedure uses the VBA DateSerial function to determine leap years. This approach is widely accepted, but may cause problems because it is relying on side effect behavior of how DateSerial is implemented. Because we have seen that Microsoft date time logic can change between versions, or when system-level components (such as the OLE Automation libraries) change, this approach should not be considered 100% reliable:

A better approach is to write your own routine that does not rely on language or operating system components.

Function IsLeapYear2 (intYear As Integer) As Integer
  ' Comments  : Determines if the specified year is a leap year without
  '             relying on the DateSerial method.
  ' Parameters: intYear - integer year
  '             Note: Always specify the full four digits of the year
  '             or the code cannot be considered Year 2000 compliant.
  ' Returns   : True - year is a leap year, False otherwise
  '
  On Error GoTo PROC_ERR
  
  IsLeapYear2 = False
  
  If intYear Mod 4 = 0 Then
    If intYear Mod 100 = 0 Then
      If intYear Mod 400 = 0 Then
        IsLeapYear2 = True
      End If
    Else
      IsLeapYear2 = True
    End If
  End If

PROC_EXIT:
  Exit Function
  
PROC_ERR:
  MsgBox "Error: " & Err & ". " & Error$
  Resume PROC_EXIT
  
End Function

Many database applications import and export data from other sources. Indeed, one of the most popular uses of Access is for massaging data brought in from legacy systems. Whenever you bring data into your application from an outside source, or export data out, the potential for Year 2000 issues is there.

If you are using built-in tools to import and export data, be sure to examine your application's results to ensure that they are Year 2000 compliant. For example, you can use the TransferText command in Microsoft Access to import or export text data. Or you can use Insert, File in Microsoft Word to bring in external data that may contain dates.

In all such cases, the date data is treated as strings and may cause century problems.

You must also examine your application for any place where you are using macro or module code to import or export data. Each case needs to be verified to ensure:

When data is imported from an external text source, the potential for bringing in dates with two-digit years exists. You must examine how date data is being brought into your application. Are you working with a data source that has all four digits of the year? When the application brings the data in, is the century being maintained correctly? Similarly, when you export data for use by other programs, you need to ensure that you are always writing out the full four digits of the year.

Issues also exist when using the native BASIC file routines. As you can see from the following code example, writing dates to files these routines can have the same problems. When you run the following code:

As you can see, the century is truncated because of the control panel short date settings.

Additionally, customized import/export logic contained in module code may be suspect, especially if data is being imported from or exported to a format that cannot accept four digit years.

Most custom solutions have some type of output that goes to format that people can read. This output can be in the form of HTML pages, Microsoft Word documents, Microsoft Excel spreadsheet, Microsoft Access reports, and many others.

Year 2000 compliance requires that every piece of information generated by your application for external consumption show year information correctly. Each output your application generates must be tested to ensure that, where necessary, all four digits of the year are included. The reason for this is two-fold. First, it allows the developer to spot-check the output of the application to ensure that the correct century is being used. Second, it ensures that whatever application or process uses the data doesn't have to assume the century—its indelibly implanted in the data itself by the serving application.

Examine each output format to see where dates are being used. In most cases, you should ensure that the full four digits of the year are being displayed.

Remember that changing one or more date fields in an output format to support four digit years may result in expansion of the space used on the page. This can cause the output to wrap differently—especially when the date is used in the header of a report. Be sure to preview each output format you modify to ensure it still fits within the defined margins.

As mentioned earlier, your application is not the only thing that can be suspect. You must ensure that all data is correct and Year 2000 compliant. Data can take many forms in your applications:

If you Microsoft Access or the Microsoft Jet database engine, it is helpful to have a list of all date/time fields in your database. This makes it easier to spot date usage in other parts of your application. For example, if you have a textbox control on an Microsoft Access form that has no InputMask, Format, or Validation Rule property settings that identify it as date-related, you can look in your list of date/time fields to see that the control is bound to a date/time field. Similarly, with a Microsoft Visual Basic application, look for the use of the data control to identify if date/time database fields are being used.

Often, data import and programmatic date routines place date data in tables in a field type other than date/time. This is most often seen in the form of text fields that hold dates. This is a huge source of problems because strings do not represent centuries reliably and will most likely fail in the year 2000. To solve this problem, you must identify all fields in your tables that are not date/time fields, yet are used to store data. Common formats for suspect data like this are:

After you have identified all text fields, take a look at the number fields. Often date data is hidden in number fields.

Once you have identified all non-date/time fields used to store date data, plan the steps necessary to convert this data to date/time fields. Before doing the actual conversion, be sure to understand what is going to happen to dates that have only two digits stored—will the resulting century applied by your application be correct for your data?

If a date/time field (or a non-date/time field used to store date/time data) is used in a Primary Key, the effects of the Year 2000 issue can be disastrous. This is because date/time data is typically added to a multi-field primary key to make a unique key. For example, an order entry system may use the customer's ID and order date fields to build the primary key. If the order date field does not correctly store the century, when the century changes to 2000, records will no longer be linked correctly and the entire relational schema of the database falls apart.

Another useful technique for identifying non-date/time fields used to store date data is to use simple statistical analysis. For all the fields you identify as string or number, use the your database's aggregate functions, such as Min, Max, Sum, Avg, and Var functions to return useful statistical information about the data in those fields. For example, a Min and Max query against a suspect field would show whether or not the range of values falls into valid date range.

Many applications make use of third-party controls or libraries. These take the form of custom controls (*.OCX), dynamic link libraries (*.DLL) and in Microsoft Access, library databases (*.MDA). In Microsoft Visual Basic applications, you can also use resource (*.RES) files.

If your application uses any of these resources, you must ensure that Year 2000 issues do not exist in those components. Unfortunately, you often do not have the source code for such resources, so you will have to obtain certification from your vendor(s) that the components are Year 2000 compliant. Of course, you should never take a vendor's word for anything-double check the components yourself through testing.
Additionally, upgrading to newer versions from vendors may cause additional problems. It is common for newer versions of these types of resources to lack 100% backward compatibility-this can lead to an additional level of coding and testing.

This section identifies issues that are specific to each of the products that have not already been covered in this paper.

1. Using the Short Date Input Mask on form controls prevents data-entry of four digit years, regardless of the Windows Control Panel Short Date setting. Additionally, custom InputMask settings may preclude the entry of four digit years. This is an obvious Year 2000 problem. Similarly, controls that lack Input Mask settings will use whatever input rule is dictated by the computer's Control Panel settings-such a case is cause for concern.

2. Because Access is a database, there may be a temptation to store date/time data in text or number fields. This will cause Year 2000 problems and should be avoided. Access provides a Date/Time data type that correctly supports dates.

3. Use the Format function to ensure that dates displayed on datasheets, query results, forms and reports all use the Long Date format. This ensures that you always display the full four digits of the year.

4. The Calendar Control has shipped with Access 2, 95 and 97. It only stores years from 1900 to 2100. Under Access 2, two digit years are accepted. Under Access 95 and 97, the Year property does not do the conversion that other parts of Access does, so a year property of <1900 is considered invalid. This is a good thing because it forces the use of four digit years when using the Year property. However, the control does accept dates in the Value property with two digit years. So you must examine the use of Calendar controls for potential Year 2000 issues.

5. If you are using Microsoft Access 95, you may run into the situation where a user has unknowingly updated their OLE Automation Library, changing the rule for century assumption. See the section entitled "How Microsoft Products Assume Centuries" for more information.

6. Check all validation rules used on date/time fields to ensure that they correctly work with four digit years in the 21st century.

7. Table indexes can be created on date/time fields. Identify all potential issues associated with such fields that may contain incorrect century information.

8. Queries can group and sort on date/time fields. These need to be examined. Queries also support aggregate and functions that can operation on date/time fields. These are Sum, Avg, Min, Max, Count, StDev, Var, First and Last.

9. The Domain Aggregate functions available in Access can work on date/time fields. These are DAvg, DCount, DLookup, DFirst, DLast, DMin, DMax, DStDev, DStDevP, DSum, DVar and DVarP.

11. The Data Access Objects (DAO) programmatic interface to the Microsoft Jet database engine provides LastUpdated and DateCreated properties to show when structural changes occurred. These fields use compliant date/time fields for storage.

12. If you use Access in multiple-version environments, there are additional issues to consider. Imagine a scenario where your application is deployed in two databases, an "application" database that contains all queries, forms, reports, macros and modules, and a "data" database that resides on the network. Now complicate issues by having the application database installed locally on user machines with attached/linked tables providing access to the data database on the network. Finally, imagine that some of your users are using an Access 2 version, some are using an Access 95 version, and some users are using an Access 97 version.

Because of the differences between each version, you could have multiple data entry operators seeing completely different results when your application is run. In fact, up to three completely different rules for determining centuries could be at work.

Such as situation is almost guaranteed to cause Year 2000 problems. If you are running in a mixed version environment, you must understand how each version of Access interprets centuries. Then, scan your application's logic to ensure that each version's "application" database is in sync with the others.

13. Microsoft Access supplies import/export functionality through the File, Export menu item, and through the TransferText action. Using these facilities, you specify the file to use and various options. One of these options is "Four Digit Years". If this option is turned off, date being imported or exported is stripped of its century information. Worse, this option is turned off by default.

To solve this problem, you need to ensure that users never have the option of seeing this user interface-if they do, the chance is too great that they will forget to click the "Four Digit Years" checkbox. The alternative is the use the capability to save Import/Export settings as specifications. The application's developer should set up the specification, ensure that the "Four Digit Years" option is turned on, and from there on out the application should use the saved specification.

If follows that as part of the certification process, you need to examine each saved import/export specification to ensure that the "Four Digit Years" option is turned on.

14. When Microsoft Access imports text data containing dates, it tries to identify date/time fields and convert the source file's mm/dd/yy two-digit year into the a four-digit year using whatever rule is correct for that version of Microsoft Access. For example, if you import a file using Microsoft Access, version 2 , and the data contains dates with two digit years, all resulting date data will have 19 tacked in front of it. If Microsoft Access 97 imports the exact same data, the resulting date data will have 19 or 20 tacked on the front depending on the value of the two digit year in the source file (the sliding window algorithm). The same set of problems exist on data export operations.

The problem is compounded by the fact that when importing from or exporting to text files, Microsoft Access offers the option called "Four Digit Years". By default, this option is turned off, meaning that:

15. The TransferText action, available in Microsoft Access macros and module code, has a potential Year 2000 problem. When you export text data using this action, all century information is set to the 20th century-even when the source text file has full four digit years. This happens if you don't specify the named of a saved import/export specification that has the "Four Digit Years" option turned on. When you use TransferText, you will notice how the century information has been changed. The result is bad data. Microsoft Access 2.0 does not use the Windows Control Panel Short Date setting. So even if you have your Short Date set to show four digit years, TransferText ignores the setting and always exports two digit years.

Versions 95 and 97 of Microsoft Access handle TransferText differently. Both of these versions respect the Control Panel Short Date setting. So if the computer has a short date set to show four digit years, the full four digits will be exported with TransferText. However, if Short Date is set to the default two digit year format, TransferText has the exact same issue as in Microsoft Access, version 2 and you can lose data.

1. Microsoft Excel treats 1900 as a leap year, even though it isn't a leap year. This was a design decision based on the desire to be compatible with Lotus 1-2-3, which originally introduced the (incorrect) identification of 1900 as a leap year.

2. The most common date format in Excel is the Short Date format as defined by the Windows Control Panel. This format cannot be relied upon to show the full four digits of the year.

3. The rule used for assuming centuries on two digit years differs between Microsoft Excel 5, Microsoft Excel 95, version 7 and Microsoft Excel 97, version 8. In environments where different versions of Excel are in use on shared data, this can cause problems.

4. If you record a macro that includes date entry, the macro will only record the date information according to the Windows Control Panel Short Date setting, which results in a two digit year when the macros is played back.

5. If you use the Format function to format a date like "Dec 98", the format will provide incorrect data when the year 2001 arrives. This is because 98 is too large a number to be a day of a month, and Microsoft Excel will attempt to treat it as a year.

6. Defined names store references only as text strings. Since they do not store dates as serial values, they are vulnerable to century issues when a two digit year is specified. Using defined names in this way is also problematic because the use of a date format with an order other than month-day-year will cause miscalculations. Recommended usage is to define the name referring to a cell containing a serial date, which avoids both of these problems.

7. You should avoid storing two digit dates in external formats that only support strings. For example, the TXT, DIF, CSV, and PRN formats cannot correctly store or represent two digit dates, since they deal only with strings.

8. Microsoft Excel support Visual Basic for Applications (VBA) which uses the OLE Automation Library (OLEAUT32.DLL) for determining centuries on two digit years. Because Microsoft Excel does not use the OLE Automation Library, and VBA does, this can cause a mismatch when you pass two digit years between Excel and VBA. To solve this, always include the full four digits of the year in such applications.

1. Microsoft Outlook 97 uses two different windowing techniques based on the type of data being entered. For scheduling of tasks, events and meeting events, the window includes 30 years back from the current date and 70 years forward. Birthdays in the Contacts part of Outlook use a different window because birthdays are usually biased towards past years. When you enter a two digit year in the Birthday field, the window is between ninety-five years back and five years forward from the current date.

2. An issue exists with Microsoft Outlook 97 you should be aware of. If a scheduled event spans the century boundary, the event will not be handled correctly. To fix this problem, obtain and install the update OUTLLIB.DLL available from Microsoft. The update file is version 8.03.

3. Internally, Microsoft Outlook 97 evaluates dates as true date/time information, not as strings. However, your custom solutions using forms, you should check for issues where two digit years are used.

1. Most features in Microsoft Word that rely upon dates use an internal 32-bit integer storage scheme that correctly handles dates and years. However, you can type dates into a document, or import date data from another application that results in dates stored as strings. When a date is stored as string, it cannot correctly be interpreted by Microsoft Word as a date. An example of this problem is a table that is sorted by a column that has string dates--the sort will not be correct. Additionally, dates inserted with the Insert Date/Time command are placed in the document as text.

2. Microsoft Word version 6, and Microsoft Word 95, version 7 use the following rule to determine the century for two digit years: The two digit year "00" is interpreted as the year 2000, all other two digit years are interpreted as being in the 20th century. This rule is used by Microsoft Word when performing a table sort, and when using Date formatting (picture switch) of Quote Fields.

3. Microsoft Word 97, version 8 uses the following rule to determine the century for two digit years: Two digit years from 00 to 29 are assumed to be in the 21st century, all others are assumed to be in the 20th century. A date window of 1930-2029 is used when interpreting two digit year when using the Date formatting (picture switch) of Quote fields.

4. Saving a document to an earlier version of Microsoft Word, or upgrading an older document to a newer version has no negative effect on dates within a document. Note that all date field information and file properties are retained and correctly recognized.

5. Microsoft Word version 6 has a built in File Find utility. Using the Advanced Search capability of this utility does not allow the input of four digit years. Because all years are converted to the 20th century, with the exception of "00" which is converted to 2000, you cannot enter dates outside the 20th century in the Advanced Search dialog, other than 2000. This issue does not exist from version 7 (Microsoft Word 95) onwards.

6. Custom formulas, macros and code within a document that deal with dates need to be examined for potential Year 2000 issues.

The following lists web-based resources for more information on the Year 2000 problem:

As you can see from the material presented in this paper, certifying your applications for the Year 2000 is not a trivial task. So is the effort worth it? The obvious answer to this question depends on how important your desktop based applications are to your organization. If the answer is "not important", then don't worry about the problem. However, for the majority of us, our desktop database applications are very important, and in some cases critical, for our success. While great effort is required, the payoff of a working application is worth it.

Copyright © 1998, FMS Inc. All rights reserved. This information may not be republished, reprinted or retransmitted in any form without the express written permission of FMS Inc. The information provided in this document is provided "as is" without warranty of any kind.


	Quick Find:		Search for:

Last modified: July 13, 2005