Year 2000 Issues in Microsoft Access

By Luke Chung
President, FMS, Inc.
LChung@fmsinc.com

Overview

Contrary to popular belief, just because Access is on a "modern" PC desktop does not make it immune from Year 2000 problems. There are many areas in Access that make Access databases prone to Year 2000 problems. Although it is unlikely that Access databases will crash after 1999, many may run incorrectly or inaccurately.

Microsoft acknowledges Year 2000 issues in every version of Access and even classifies Access 2.0 as Not Year 2000 Compliant. Regardless of whether Access is compliant or not, if an Access developer did not properly consider Year 2000 issues, their databases may fail after 1999.

With proper analysis and adjustments, all Access databases, regardless of version, can be made Year 2000 compliant. This paper discusses what you need to consider and how to make your databases Year 2000 compliant:

• Common Year 2000 Myths for Access
• Two-Digit Year Assumptions in Access
• Additional Two-Digit Year Oddities
• Microsoft's Year 2000 Patch for Access 2.0
• Achieving Year 2000 Compliance in Access
• Analyzing Your Database
• Fixing Your Database
  • Field and Control Properties
  • Data Import/Export Routines
  • Primary Keys and Secondary Indexes
  • Inter-Application Links
  • Code and Use of Explicit Dates
• Additional Resources
• Conclusions

Common Year 2000 Myths for Access

Many Access developers and users assume Year 2000 does not apply to them. They have made fun of "dumb" Cobol developers who delivered non-compliant applications back in the 1960’s, 70’s and 80’s, without realizing they were delivering non-compliant Access databases in the last year or month. Whoops! This delusion is caused by several incorrect assumptions:

Access Runs After 1999

Just because your database doesn’t immediately crash when the clock is set forward does not mean it’s ready for Year 2000. In general, Access databases will run after 1999. They just may not run correctly or accurately, which is actually a more insidious problem. Unless you’ve examined every place you are using dates in your database and verified that four-digit years are being used, your database may not ready for Year 2000. Some examples of problems include input masks that don’t allow entry of four-digit years, controls not wide enough to display all four-digits of the year, import/export specifications that ignore century information, manipulation of dates in queries, macros, module code, etc.

Access Tables Store Dates with Four-Digit Years

Although date/time fields in Access tables store dates with century information, that’s just the beginning of Year 2000 compliance efforts. How the data gets into those fields is the question. Are users entering or viewing dates with just two-digit years? If so, are your application and the user’s century assumptions the same? Can users enter data outside your window of two-digit year dates?

It’s Just a Data Entry Mistakes

Data entry clerks can be trained to use a piece of software and taught the two-digit year assumptions. Unfortunately, most people use more than one piece of software and even within Access, each version has different century assumptions. Just determining what the underlying program is difficult in many cases, much less the version. How to keep all the rules straight for every program is bound to result in bad data entry. The only solution is to force entry of four-digit years everywhere. Who cares if an extra "19" or "20" needs to be entered? It’s the only solution to guarantee the correct data is stored.

I Don’t Enter Dates

Even applications that don’t involve data entry may have serious Year 2000 issues. The data has to come from somewhere. In Access 2.0, Access 95, and even Access 97, the default setting for file import/export specifications ignores century information in dates. You must specifically check the "Four Digit Years" option to include century information. Because of this, you can easily export a table and import it back with the dates converted to the wrong century.

If you import/export data through other files like Excel spreadsheets, you need to verify the data is transferred correctly. And if you link to Access tables, you need to verify the data gets into those tables correctly. This is particularly important if you are linking to an MDB that may be receiving data from a variety of programs such as Access 2.0, Access 95, Access 97, Visual Basic, etc.

Finally, if you use the clipboard to copy and paste date data, you need to verify that works correctly.

Reports Don’t Need Four-Digit Years

We’ve heard this over and over. While header and footer information showing the current date with two-digit years may be acceptable because everyone understands the current year, the dates printed in a report should always show four-digit years. If not, there is no way to determine the century of the stored date. Sure, the value of "2/12/00" should be in the year 2000, but the report does not verify that. If the date is actually stored as 1900, other reports or analysis could miss that data.

When a two-digit year is printed, an assumption is being made about the century. Each Access version has it’s own assumptions, as do many other programs. Reports get passed around. Do the reader’s of the reports know what program and version generated it, much less the century assumptions made?

Control Panel Short Date Setting Sets Four-Digit Years

The Windows Control Panel lets you change the default short date setting from two-digit year to four-digit year. Some people think this solves the Year 2000 problem in Access. Nothing could be further from the truth.

First, even if the setting worked everywhere, many forms and reports need to be adjusted so the full four-digits of the year is displayed without truncation or word-wrapping. Second, not all parts of Access respect this setting. The Medium Date format is always two-digit years. How your code manipulates dates and years is also totally independent of the setting. 

Finally, relying on this setting is not a reliable way to bullet-proof your application. There is nothing to prevent the user from resetting their short date setting back to two-digit year. In Windows 98/NT, users can actually set their own two-digit year Window, which means your application running on two different machines can behave very differently if you can't see all four digits of the year. The only solution is if your database controls four-digit years and does not rely on external settings.

It’s Not Like I’ll Lose Data

Some people think if dates are entered and stored with the wrong century, they can easily fix it later and it’s not as tragic as losing data. We could argue with that, but there are cases where data may be lost or damaged in a big way. The most obvious is if date fields are in indexes and table relationships with cascading updates and deletes. Entering a bad date could cause extensive and undesirable changes.

It’s Easy to Use Dates with Four-Digit Years Everywhere

Once you accept that the only solution is to use four-digit years everywhere, you’ll want to implement that in your databases. Unfortunately, Access does not make it easy. First, you need to find the places the changes need to be made. That involves examining every object.

Once you find the places to change, you’ll discover that Access does not include Input Masks or Format settings supporting four-digit years. You’ll have to manually enter those values rather than picking items such as Short Date or Medium Date. Worse, if you are building applications to distribute beyond the US, you cannot easily handle the month/day order as specified in the Control Panel. The built-in Access format settings take that into account and switch accordingly, but you have to write your own code to handle that. You also need to add validation rules to make sure reasonable years are entered once four-digits are required.

Two-Digit Year Assumptions in Access

By default, every version of Access accepts entry of dates with two digit years and stores them in date fields with four-digit years. Unfortunately, every version of Access makes different century assumptions so entering or viewing the same data (e.g. 1/1/00) may actually be for dates a century apart.

Access 2.0

When dates are entered with two-digit years, Access 2.0 makes this assumption:

Dates with two digit years are always assumed to be in the 20^th century.

Therefore, to enter January 5, 2000, you need to enter "1/5/2000", "1/5/00" would be January 5, 1900. A big problem.

To handle dates correctly, Access 2.0 databases must show four-digit years and require entry of four-digit years. Date fields and controls without an Input Mask or an Input Mask that limits entries to only two digit years are problems that must be fixed. Otherwise, bad data is almost guaranteed to be entered.

All date fields and control must also be widened to accommodate four-digit years.

Access 95 (version 7.0)

With the release of Access 95, date rules were taken out of Access and put into a OLE Automation file (OLEAUT32.DLL). The idea was to make the date rules independent of each product and under operating system control. The new solution made this century assumption:

Dates with two-digit years default to the current century (as defined by the clock)

The idea was that in the 20^th century, entering "1/1/99" would be interpreted as 1999 and in the next century typing "1/1/00" would be interpreted as 2000.

Unfortunately, this was entirely unusable:

1. Every machine’s clock is different.
2. Dates in this century can be entered with two-digit years, but when we are in the next century, the same dates need to be entered with four-digit years.
3. How do you teach people to enter dates one way in December 1999 and change it completely in January 2000?

Microsoft quickly realized this was a mistake and created a revised DLL that changed the rules to a sliding window:

Dates with two-digit years between "30" and "99" default to the 20^th century, while dates between "00" and "29" default to the 21^st century.

This DLL is installed with the Access 95 ADT, which means a runtime version of an Access 95 database may behave differently from a regular Access 95 copy. The revised DLL is also installed with other products such as Office 97, Outlook, Internet Explorer, etc. Therefore, an Access 95 application could be subject to either two-digit year assumptions depending on the machine and the programs installed on it. It is completely outside the control of your database or Access. Can you explain to a user that they need to be aware of the DLL that’s installed before using your application? Basically, two-digit years should never be used in an Access 95 database.

NOTE: Excel 5.0 and Excel 95 (version 7) does not use the DLL but has a sliding window between "20" and "99" as 20^th century dates. This means if you use Excel and Access, you need to be aware that different rules that apply across Office products.

Access 97

Access 97 uses the revised DLL’s sliding window described in the Access 95 section:

Dates with two-digit years between "30" and "99" default to the 20^th century, while dates between "00" and "29" default to the 21^st century

This may be a reasonable assumption for most dates. However, you definitely need to use or allow the use of four-digit years for birth dates, and if you are doing any projections such as 30-year mortgages or bonds, you’ll exceed 2029 very soon. But even that is not enough to insure your application will handle dates correctly.

Since the date rules are controlled by a DLL, Microsoft can update the DLL at any time and it may appear on your user’s machine when they install a program entirely unrelated to Access. Can you anticipate how your Access application will work with the new date rules? Of course not! Use four-digit years everywhere and you won’t have to rely on this.

Two-Digit Year Conclusion

By default, Access allows dates to be entered and displayed with two-digit years. As we move to the next century, people will get used to dealing with dates with all four-digits of the year. By definition, any shortcut that omits century information makes a century assumption. As we’ve seen, assuming the user, developer, and computer can determine, much less use, the same Access/Office assumption is almost impossible. Compound the problem with users running several types of applications from PCs to mainframes, and software from other vendors, and it’s impossible to remember the assumptions in every product.

As a developer, if you allow dates with two-digit years, you need to handle dates outside the century assumption. This could involve a lot of code and maintenance hassles.

The only solution, and most straightforward solution for everyone, is to use four-digit years everywhere.

Additional Two-Digit Year Oddities

Even if you understand the data entry rules for two-digit years described for each version of Access, there are additional complications.

Handling Assumed Years

As a shortcut, Access is designed to let you to enter just the month and day. It automatically fills the year with the current year. For instance, in a date/time field, if you enter "1/1" and leave the field, the current year is applied and "1/1/1999" is stored. At least, that's the design.

Access 2.0 Bug

In Access 2.0, the current year assumption works, but not after 1999. If the current date is in the year 2000, typing "1/1" into a date field and departing, stores "1/1/100", not "1/1/2000" as you would expect! Make sure you have the date format set to show four-digit years to see this. Year 2001 gets stored as 101, etc. It's extremely important for Access 2.0 applications to have validation rules for each date field where data entry may occur. A simple validation rule such as "> 1/1/1900" would prevent this problem.

This bug was fixed in Access 95 and 97.

Default Display of Dates

The way dates are displayed differs based on which version you are using. The previous section described how two-digit years are interpreted during data entry. The display of dates in tables and modules do not strictly follow those rules.

Using the default format property (that is, no value), dates within these ranges are shown with two-digit years. Dates outside this range are shown with four-digit years:

Notice how in Access 95, two digit years are used for a 70 year window with dates after 1999 shown with four digits. In Access 97, a 100 year window is used to display dates (matching the data entry window), but for module code, a 70 year window is used in Windows 95 (a 100 year windows is used in Windows 98).

Query Criteria with Partial Year

If you use a LIKE statement in a query to select years, you are probably in trouble. This technique actually relies on the display format of days which is controlled by your Control Panel settings and Access century assumption rules. See how Access behaves different for the same criteria in by version:

The odd results are:

1. For Access 2.0, and search for a date in the 1900s with four digit years fails. Only two digit years may be used in the query LIKE statement.
2. For Access 95 and 97, the search for two digit years works as expected between 30 and 99, but for 00 to 29, no records are retrieved and four digit years must be specified to select dates between 2000 and 2029 or 1900 and 1929.
3. Searching on four-digit year between 1930 and 1999 retrieves nothing in all three versions.
   To avoid these problems, use the Year function on dates and select based on that rather than the string. If you can't avoid the use of these LIKE criteria, make sure you understand that after 1999, regardless of version, all four-years must be used. If you are searching for dates less than 2000, you'll need to change your query based on the Access version and which date you are searching.

To avoid these problems, use the Year function on dates and select based on that rather than the string. If you can't avoid the use of these LIKE criteria, make sure you understand that after 1999, regardless of version, all four-years must be used. If you are searching for dates less than 2000, you'll need to change your query based on the Access version and which date you are searching.

The CVDate and CDate Functions

The VBA functions CVDate and CDate convert text or numbers into dates. The result is a "real" date with a four digit year, but the string value uses the Control Panel setting for short date. That is, CDate("1/1/99") is a date variable containing the year 1999, but a string value of 1/1/99.

There are situations where CVDate and CDate return some unexpected results. In fact, CVDate and CDate are more powerful that you'd expect. They actually convert all sorts of strings to "acceptable" dates. 

For instance, if month-day order are switched, it automatically "fixes" it for you. Therefore, CDate("13/11/99") becomes 11/13/99 (November 13th). One could argue that an error message should be raised, but that's a different issue. A side-effect of this behavior is what happens with years between 01 and 31.

Obviously, there are several problems here. First, don't use CDate to fix month-day order. Second, don't use CDate to make year assumptions for you since it uses the Control Panel settings. As always, use four digit years whenever you reference a year.

Microsoft's Year 2000 Patch for Access 2.0

Because Access 2.0 interprets all two-digit years to 1900s and has several other Y2K issues, Microsoft released a patch in May 1999 to address these issues. Unfortunately, that patch caused additional problems and was replaced. To date, Microsoft has released four sets of patches:

• Version 1, May 7, 1999

• Version 2, September 30, 1999

• Version 3, November 8, 1999

• Version 4, December 3, 1999

The latest patch and information on previous patches is available for free from the Microsoft KnowledgeBase article Q231408 (http://support.microsoft.com/support/kb/articles/Q231/4/08.asp). 

Patch Summary

The first three patches Microsoft released contained serious problems ranging from not handling Y2K issues correctly to database corruption problems. Microsoft now recommends that these older patch be uninstalled. In fact, this is a Y2K issue in and of itself and you need to make sure your users do not have them installed on their machines.

On December 3, 1999 Microsoft released a new patch. We have not performed a full review of this patch but have confirmed that the assumed year bug is fixed. Since this patch has not been extensively tested in real-world situations, we highly recommend that you make sure your applications explicitly use 4-digit years everywhere so you don't rely on the date assumptions of any program.

The following information applies to the originally released patch in May that has since been removed. 

Do Not Use It!

There are many problems with the patch, which is described later. Regardless, do not use it! 

Although Microsoft no longer supports or distributes this patch, it doesn't mean your users don't have it already installed on their machines! We basically have a new Year 2000 problem to contend with for Access 2.0 environments. Here's a simple function to test whether the new rules are installed:

Private Function IsDatePatchInstalled () As Integer
  ' Comments: Test to see if the patch for Access Basic code is installed
  ' Returns : True if patch is installed, FALSE if not

  Dim intYear As Integer

  intYear = Year("1/1/00")

  IsDatePatchInstalled = (intYear = 2000)
End Function

The IsDatePatchInstalled function returns TRUE (-1) if the patch is installed, or FALSE if not. It works by seeing how Access Basic converts a two digit year date string into a date (year). For the original Access 2.0 installations, the 1/1/00 date is considered 1900. For the patch, it's considered 2000.

Add this function to your application and run it in your startup routines. If it returns TRUE, your program should stop and notify the user or system administrator to fix the environment.

About the Patch

The patch is fairly small (255K) and includes three files:

1. MSABC200.DLL: an updated Access Basic file.
2. MSAJU200.DLL: an updated Access Jet Utilities file.
3. Readme.txt: installation information and the End User Licensing Agreement\

Simply replace the corresponding files in the Access 2.0 and Windows\System directories. With these new files, these changes are made to Access 2.0 for dates with two digit years entered or imported:

• 00 to 29 resolve to the years 2000 to 2029
• 30 to 99 still resolve to the years 1930 to 1999

Limitations

While these changes are nice, they are some very serious limitations to using these patches:

Inconsistency Between Display/Printing of Dates versus Data Entry

There is no change to the way dates with Short Date format are displayed. The default ShortDate format still shows dates two-digit years for 1900 through 1999.  That is, the way you enter a date is different from how you see it. For instance, enter “1/1/29” and it appears as “1/1/2029” which is fine, but enter “1/1/1929” and it appears as “1/1/29”. Therefore, if you see “1/1/00” you need to know that is still 1900, even though entering that string “1/1/00” means 2000. This lack of consistency between viewing data and data entry can easily cause errors in data entry or interpretation.

Inconsistency in Access Basic

There is also a problem in code. CVDate always returns a date string for a date in the 1900s regardless of whether the patch is installed or not. Look how CVDate works in both situations:

Notice how with the patch, CVDate of "1/1/00" is 2000 which is expected, but CVDate of "1/1/1900" is "1/1/00" in 1900. It should return 1/1/1900 (which it does in Access 97). This is a very dangerous bug in this patch.  There are similar problems with the use of dates in query criteria.

Does Not Respect the Windows 98/NT Control Panel Setting

This patch does not respect the Windows 98/NT Control Panel that can change the pivot year from 1930 to another year. Therefore, Access 2.0’s date conversion routine can differ from newer versions of Access on the same machine. For instance, entering “1/1/40” may be 1940 or 2040 depending on which version of Access is being used.

Patch May Not be Installed

If you rely on this patch, you must require all users to have it installed on their machine. Even if it’s correct today, if they change machines or get a new machine, they can easily lose the patches.

Does Not Address Assumed Year

The most critical Year 2000 data entry problem is the assumed year. As described earlier, after 1999, just entering the month and day for a date field and departing puts in the wrong assumed year (e.g. 100 instead of 2000, 101 instead of 2001, etc.). These patches do not address this issue.

Does Not Address Other Issues

Other Year 2000 issues that affect Access 2.0 databases, such as exporting date data, printing date data, control widths to display four digit years, and problems in code, are not affected by this. 

Your Access 2.0 Databases May Now Behave Differently

Regardless of whether you use this patch or not, you need to anticipate users that may or may not have this patch installed. All of a sudden, your Access 2.0 databases can behave differently on different machines. You cannot assume that all two-digit years will be in the 1900s. Plan and train your people accordingly. Note, that this also affects code. For instance, CVDate("1/1/00") gives a date in 2000 or 1900 depending on whether the patch is installed or not.

Solution

The solution is simple. Don't use this patch. Make sure you use four-digit year everywhere and you'll be able to handle Year 2000 situations without this patch. 

Achieving Year 2000 Compliance in Access

Year 2000 compliance involves much more than Access. There are hardware BIOS issues to check (some machines won’t run properly after 1999), there may be network hardware and software issues, and other applications you need to run. This paper will only focus on the issues that affect Access: the basic definitions of Year 2000 compliance, and how to achieve it.

Three Requirements for Year 2000 Compliance in Access

As a starting point, we suggest the following three prerequisites for Year 2000 compliance. If you can ensure that your application meets these requirements, you can feel reasonably confident that your database applications are ready for the Year 2000:

• All date input require four digit years
• All date logic uses four-digit years
• All dates are displayed or output with four-digit years

All date input require four digit years

Any part of your application that can accept dates, such as date-entry forms and imports of external data, must require four-digit years be provided. For example, all data-entry forms should use the InputMask and ValidationRule properties to require Year 2000-compliant dates. They should also be wide enough to enter four-digit years.

Additionally, import routines must verify that the input data source (whether it be a text file, spreadsheet, or other format) contain the full four digits of the year, and the century information is being used.

If your program accepts two-digit years, there is a very big risk that it is not Year 2000 compliant and may fail when dealing with 21st century dates.

Handling input of two-digit years

In some situations, the input of four-digit years may not be possible. If your application requires data entry of many dates, your requirements may specify two-digit years. Additionally, your application may rely on external data from other sources that do not provide four digit years.

In these cases, your application needs to accept two-digit years. If this happens, you must use your own custom program code to ensure that the correct century is assumed for the dates entered, and you must handle how dates outside the 100 year two-digit window can be entered.

The definition, testing, and use of your own custom solution is critical: you must never rely on Access (or any program beyond your control) to assume the century for you. Otherwise, the rule may change without your application or users knowing it.

All date logic uses four-digit years

Anywhere your application manipulates dates, such as in queries, SQL, expressions, macros, or code, you must verify you are correctly working with century data. For example, you should never convert a date to a string and use string functions to grab the two-digit year on the resulting data. When working with dates, always use the intrinsic date functions such as Date, CVDate, Year, Month, etc.

All dates are displayed or output with four-digit years

If your application displays dates on screen, reports, file exports, web output, or communication with other programs, you must ensure that those outputs include four-digit years. This is crucial. If your application properly implements the first two items (date input and date logic), it would contain correct century information. Any output from it should include four-digit years to eliminate any ambiguity. Always displaying four-digit years is also useful for verifying the correct century is being used.

Steps for Achieving Year 2000 Compliance

For Year 2000 compliance in Access, your databases should display, accept data input, print, import, export, and programmatically manipulate dates with all four-digits. That sounds simple, but you’ll find that it is not easy to implement. To meet this goal, you need to follow several steps:

1. Analyze the objects in your database and related objects for Year 2000 and date related issues.
2. Prioritize or assign a risk level to each issue detected
3. Determine which issues will be fixed
4. Determine the fixes (solutions) to implement
5. Implement the fixes and test them
6. Certify individual objects are Year 2000 compliant
7. Test the entire application after all the fixes are applied
8. Certify the database is Year 2000 compliant

Obviously, this can be a very time consuming and error-prone process. Care must be taken to make sure these steps are properly followed without adding new bugs into your database.

Analyzing Your Database

The first step toward achieving Year 2000 compliance is to determine where you are using dates. This can be a very time consuming process because dates can be used almost everywhere in Access. Basically you need to carefully examine every object and look for date usage. These include:

Tables

• Find Date/Time Fields
• Determine Non-Date/Time Fields that contain date data:

  Imported text fields (e.g. mainframe dates: MMDDYY)

  Partial dates such as credit card expiration dates (e.g. mm/yy)

  Years in numeric or text fields (e.g. fiscal year, or graduation year)

• Analyze Field Properties
  Once you know which fields are date related, you need to verify the Format, Input Mask, Validation Rule, Validation Text, and Default properties are set to display and require four-digit years.
• Check your data to make sure you don’t have bad date data.
  Examining the minimum and maximum value of each field can reveal bad dates.
• Generate Table Field Cross-Reference
  Once you know which fields are date related, you need to know where the fields are used across queries, forms, reports, macros, and modules.

Table Relationships

• Determine if date fields in indexes will cascade or delete data if bad data is entered.

Queries

• Examine queries that use date fields
• Examine date criteria
  Especially LIKE statements on partial dates (e.g. Like "*/99")
• Examine the use of date related Access Basic/VBA function calls or user defined functions
• If users can edit data directly from queries, verify the field properties of dates are set to display and require four-digit years.
• Examine objects that use queries with date related information

Forms

• Verify how dates are used in the record source
• Verify if and how dates are used in the row source of combo boxes and list boxes
• Verify the controls where dates are displayed or entered have the correct Format, Input Mask, Validation Rule, Validation Text, and Default properties
• Verify the date controls are wide enough to display four-digit years without truncation

Reports

• Verify how dates are used in the record source
• Verify the date controls are wide enough to display four-digit years without truncation or word-wrapping

Macros

• Detect use of date related data or commands
• Detect dates used in the Condition clause
• Don’t use TransferText command

Modules

• Detect any use of date related Access functions
• Verify use of explicit dates
• Detect use of date variables
• Examine code that performs file import/export or manipulates imported data
• Any other manipulation of dates, date fields, or date data

External File References

• Examine dates in all import/export files, spreadsheets, etc.
• Determine the ActiveX controls and DLLs used to verify date usage
• Verify usage of library code in VBA Reference libraries
• If you are using attached/link tables, verify other applications that use those tables

File Import/Export Specifications

• Verify import/export specification override the default setting and handle dates with four-digit years.

Fixing Your Database

Once you’ve determined where you manipulate dates, you need to verify it is using four-digit years. If not, you have to fix it. The solution is easy to understand. Two digit years must be converted to four-digit years. Unfortunately, it’s not always easy to implement. There are several areas to address:

• Field and Control Properties
• Data Import/Export Routines
• Primary Keys and Secondary Indexes
• Inter-Application Links
• Code and Use of Explicit Dates

Field and Control Properties

The most common problems are related to how dates are displayed (formatted) and entered (Input Masks and Validation). Here are some formats that are compliant and not compliant:

Diagnosing and Fixing Object Property Values

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 a form control’s DefaultValue property, you can specify a value to be placed in the control if the user does not specify one. Two types of values can be placed in this property:

Literal Values

Literal values are strings of text that are to be taken literally. For example, if the DefaultValue property contains 07/01/98, a string of "07/01/98" is placed in the control.

Expressions

Expressions are strings that are evaluated by the program and return a value. For example, in a control’s DefaultValue property you can enter the value Now, which runs the Now() function and places its results (the current date and time) in the field. This is an example of an expression calling an intrinsic date function.

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-conforming date to be stored or acted upon.

Additionally, you should check all validation rules used on Date/Time fields to ensure that they work correctly with four-digit years in the 21st century.

Certifying Data Entry Objects

A major Year 2000 problem is how your program accepts dates. The user interface in most Access applications does not require the entry of dates with the full four digits of the year. At worst, the application has Input Masks or custom logic that forces two digit years only.

Date fields entered by a user must require input of four-digit years. This ensures that the operator, not the computer, specifies the century. Similarly, anywhere a date is shown, whether on a form or report, must be displayed with four-digit years.

Examining Reports

Year 2000 compliance requires that every piece of information generated by your application, including displays of dates on reports, show four-digit years. Some people think that reports don’t need to show four-digit years because the two-digit interpretation is clear. Certainly, no data entry occurs or reports, but without displaying four-digit years, you risk hiding bad dates (dates with the wrong century). Maybe the data was imported or entered incorrectly. Without reports showing your dates with four digit years, you cannot verify the data is correct.

Finally, remember that reports can group and sort on Date/Time fields. Non-Year 2000 compliant data or expressions can cause report groupings to fail.

Supporting Four-Digit Dates

Anywhere dates are entered or displayed, four-digit years should be used. One of the most serious Year 2000 compliance problems in Access is with table and query fields, and form and report controls that receive or display date data. By default, fields and controls do not restrict the data that can be entered. That means two-digit years can be entered or displayed. The application or user needs to make an assumption about the century. How data outside the assumption window is handled is also not clear.

The most common Year 2000 issues are associated with these properties:

• InputMask Property
• Format Property
• Validation Rule and Validation Text Properties
• DefaultValue Property

These properties must be properly set to make sure Year 2000 issues are handled.

Input Mask Property

The Input Mask property controls how data is entered. For Year 2000 compliance, the Input Mask on date fields should require four-digit year entries.

Form controls with the common Input Mask set to Short Date or Medium Date prevent entry of four-digit years, regardless of the Windows Control Panel Short Date setting. For Access 2.0 applications, this is a serious Year 2000 problem because users will be unable to enter dates after 1999. Entries of "00" are saved incorrectly as 1900.

Custom Input Mask settings may also preclude the entry of four digit years. Similarly, controls that lack Input Mask settings use whatever input rule is dictated by the computer’s Control Panel settings-another cause for concern.

You need to select an Input Mask that is appropriate for your situation. Here are some examples:

Format Property

The display of dates is controlled by the Format property, which is independent of the InputMask property (for instance, you can require input with two-digit years, and display four-digit years, or vice versa).

Any control that displays dates should have an explicit format property setting showing all four digits of the year. This ensures that the operator can spot-check dates and identify anomalies as data is being entered.

Common date formats that are not Year 2000 compliant include Medium Date and Short Date settings. Medium Date uses a two digit year display, and Short Date is subject to the Windows date settings which may be different on each machine.

Unfortunately, Access does not provide date formats that display four-digit years. You have to hardcode the format string to include four-digit years. This table shows the main formats and the way they display date data. Other formats are provided to show four-digit years. You can use these or your own to achieve Year 2000 compliance.

Validation Rule and Validation Text Properties

If your Input Mask requires four-digit years, you should add a validation rule. Otherwise, data entry errors may occur where dates in the wrong century are entered. For instance, if someone enters a date for 1999 with "99" and tries to tab to the next control, the Input Mask prevents them from leaving the control. This is correct, but if the user isn’t careful (especially during heads down data entry) and enters the next numbers, a date in the 99th century may get stored.

The Validation rule you use depends on your data and how tightly you want to restrict the values. You may even use different ranges for different fields whether they are historical versus future dates. Just specify the range of values such as:

• Between #1/1/1900# and #1/1/2100#
• Between #1/1/1800# and #1/1/2100#
• Between #1/1/1998# and #1/1/2050#

Similarly, you should set the ValidationText property to provide helpful information to allow the user to identify why the rule failed.

DefaultValue Property

The DefaultValue property sets the initial value for a field. Like other properties, DefaultValue can contains values and expressions. You should verify this property setting for each object to ensure that four digits are correctly specified when dates are used.

Control Width

You must also be aware of the width of your form and report controls. By converting from two-digit to four-digit years, your controls may need to be widened to accommodate the extra digits. The required width of a control is based on its date format, font, point size, and style. This may impact other controls so you may need to redesign your forms and reports to handle this. On reports, if your controls are not wide enough, the data is either truncated or word-wrapped. Control width must be checked!

Data Import/Export Routines

Many database applications import and export data from other sources. Indeed, Access is often used for analyzing data from mainframe systems. Whenever you bring data into your application from an outside source, or export data, the potential for Year 2000 issues exists.

Problems with Text Import/Export

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.

Microsoft Access supplies import/export functionality through the File menus Get External Data and SaveAs/Export, and through the TransferText action. Using these facilities, you can specify the file to use and various options. One of the options is "Four Digit Years". By default, this option is turned off and is not Year 2000 compliant. By default, dates are imported or exported without century information!

To address this issue, make sure your users never see this interface. If they do, the chances are too great that they will not click the "Four Digit Years" checkbox. The alternative is to use the capability to save Import/Export settings as specifications. The application’s developer should set up the specifications, ensure that the "Four Digit Years" option is turned on, and from then on the application should use the saved specification.

In macros and modules, the TransferText command needs to be detected and verified to be using the correct import/export specifications.

Review Programmatic (Custom) Import/Export Routines

You must also examine your application for places where macro or module code imports or exports data. Each case needs to be verified to ensure:

• For exports, all four digits of the year are output.
• For imports, all four digits of the year are retrieved, or in the case where the input file has only two digit years, the correct century is assumed.

When importing data, you must examine how date data is brought into your application. Are you working with a data source with all four digits of the year? When the application brings in the data is the century being maintained correctly? Similarly, when you export data, you must write all four digits of the year.

Issues also exist when using the native BASIC file routines. The Print statement can remove century information causing non-Year 2000 compliant data.

Looks Can be Deceiving!

Look at the following code example. All four digits of the year are passed to the Print statement and it seems to be Year 2000 compliant, but it’s not!

Open "C:\testd.txt" For Output As intfile
Print #intfile, #12/13/1901#
Print #intfile, #12/13/1991#
Print #intfile, #12/13/2001#
Close #intfile

In Access 2.0, the resulting file looks like this (no 20^th century information):

12/13/01
12/13/91
12/13/2001

In Access 97, the resulting file looks like this (centuries are omitted for years between 1930 and 1999):

12/13/1901
12/13/91
12/13/2001

One way to solve this is to convert the date value to a string before passing it to the Print statement. The following modification to the above code uses the Format function to force four digit years before the value is passed to Print.

Open "C:\testd.txt" For Output As intfile
Print #intfile, Format(#12/13/1901#, "mm/dd/yyyy")
Print #intfile, Format(#12/13/1991#, "mm/dd/yyyy")
Print #intfile, Format(#12/13/2001#, "mm/dd/yyyy")
Close #intfile

Custom import/export logic contained in module code needs to be carefully examined, especially if data is being imported from or exported to a format that cannot accept four digit years.

Primary Keys and Secondary Indexes

If a Date/Time field (or a non-Date/Time field used to store date data) is used in a Primary Key, the effects of the Year 2000 issue can be disastrous. If the date field stores the wrong century, records may no longer be linked and the entire relational schema of the database may fall apart. Editing date fields with cascading updates may also trigger changes to your data into the wrong century. Dates fields used for sorting or secondary indexes will also lead to mistakes if the wrong century is stored.

In some cases, primary keys include two-digit year information. For instance, many accounting budgeting and purchase order systems include the fiscal year in the primary key (e.g. 98-123456-5). This is usually fine even when you go to 2000 because codes with 00 are unlikely to conflict with data from 1900. However, queries and reports may fail. For instance, the logic to retrieve data from last year or multiple years may rely on this number, so a query that takes the current year and subtracts from it will fail for dates 2000 or later. The solution may require changing the primary key to add the century plus all the corresponding changes, or changing all the queries, forms, and reports that may use the two-digit number.

Inter-Application Links

If you are communicating with other desktop applications, you need to examine your logic and procedures to ensure that any date data passed between the applications is correct. The following issues should be examined:

OLE Automation

You can use Access to drive other applications, such as Word and Excel, to retrieve and send data. These situations require special attention: Is date-related data being transferred? If so, examine the logic and objects to ensure that four-digit years are used. To identify OLE Automation functionality in your database, look for OLE controls on forms and reports, and module code that instantiates other applications.

Dynamic Data Exchange (DDE)

Although DDE is an outdated technology, it can still be used in Access applications. If your application uses DDE to transfer or receive date-related data, examine those parts of the application to ensure Year 2000 compliance. To identify DDE usage in your applications, locate occurrences of the DDE keywords such as DDE, DDEExecute, DDEInitiate, DDEPoke, DDERequest, DDESend, DDETerminate, and DDETerminateAll.

Other External Objects

Verify any other external objects you use are not date related or if they are, handle dates correctly. These include ActiveX controls, DLLs, library references, and linked tables.

Windows Clipboard

If your application uses the Windows clipboard, ensure that all dates are Year 2000 compliant. For example, your application may require the user to switch to another application, Copy a date to the clipboard, and paste the value into the Access application. In such a case, you need to programmatically verify the value for Year 2000 compliance.

Additionally, your application may contain module code that calls the Windows API to work with the clipboard. Such code needs to be verified to ensure that, if dates are used, four-digit years are handled correctly. To identify clipboard functionality in your database, look for Windows API Declare statements in your module code containing the word "Clipboard".

SendKeys

SendKey commands let your application perform special tasks with a single keystroke. They are sometimes used in code for clipboard operations. Look for SendKeys statements that send the Control-C, Control-V, or Control-X key combinations for Copy, Paste, and Cut.

Code and Use of Explicit Dates

Macro and module code may be the most difficult area to fix. This depends entirely on what the developer implemented.

For example, consider credit card processing where the expiration date’s year is compared to the current year. Since expiration dates are two-digit years, the general assumption is that the century is "19". Obviously this fails for expiration dates in "00" or later. This kind of error is actually easily detectable because the function fails, so you can pinpoint the error and fix it. Many other coding bugs are more subtle.

Basically, you need to examine everywhere your code manipulates dates. For macros, you also need to verify any condition statements that are date related.

Additional Resources

For more information on Year 2000 issues in Access, please consult these resources:

• Papers on Year 2000 issues in Access and Office from FMS: http://www.fmsinc.com/tpapers
  
• Year 2000 product for Access: Total Access Inspector 2000 from FMS.
  This product identifies Year 2000 problems and date usage in your databases.
  
• Microsoft Year 2000 web site: http://www.microsoft.com/year2000/
  Warning: Microsoft classifies Access 2.0 as not Year 2000 compliant, but the same techniques to make Access 95 and 97 applications Year 2000 compliant can be performed for Access 2.0 databases to make them compliant.  Also note that Microsoft's definition of compliance means their products are able to support Year 2000. It does not mean your customizations of their products are Year 2000 compliant.

Conclusions

Hopefully you have gained an appreciation for the complexity of Year 2000 issues in Access, and that two-digit years are not an acceptable solution.

Building Year 2000 compliant databases does not stop after 1999. There's nothing preventing you from writing non-Year 2000 compliant systems well into the next century. So, it is extremely important that you understand the issues and et into the habit of creating compliant systems today!

The problems are very real and depend a great deal on how you designed your databases. Once you identify the places to fix, you need to determine how to do so. Unfortunately, Access was not designed to let you easily use four-digit years everywhere. Make sure you make your fixes and test, test, test!

Good luck!

Back to Main Technical Papers Page

Copyright © 1998-1999, 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.