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:
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:
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.
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?
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.
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.
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?
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.
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.
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.
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.
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 20th 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.
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 20th 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:
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 20th century, while dates between "00" and "29" default to the 21st 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 20th 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 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 20th century, while dates between "00" and "29" default to the 21st 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.
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.
Even if you understand the data entry rules for two-digit years described for each version of Access, there are additional complications.
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.
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.
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:
Access 2.0 | Access 95 | Access 97 | |
In Tables | 1900-1999 |
1930-1999 |
1930-2029 |
In Modules | 1900-1999 |
1930-1999 |
1930-1999 or |
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).
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:
Access 2.0 | Access 95 | Access 97 | |
Like "*00" | any year ending in 00 |
same |
same |
Like "*29" | any year ending in 29 |
same |
same |
Like "*99" | any year ending in 99 |
NONE |
NONE |
Like "*/00" | 1900 |
NONE |
NONE |
Like "*/29" | 1929 |
NONE |
NONE |
Like "*/30" | 1930 |
same |
same |
Like "*/99" | 1999 |
same |
same |
Like "*/1929" | NONE |
1929 |
1929 |
Like "*/1930" | NONE |
same |
same |
Like "*1999" | NONE |
same |
same |
Like "*/1999" | NONE |
same |
same |
Like "*2000" | 2000 |
same |
same |
Like "*/2000" | 2000 |
same |
same |
The odd results are:
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 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.
String Tested | CDate Result | Date Value | Comments |
"11/12/99" | 11/12/99 | 12-Nov-1999 | Good |
"13/12/99" | 12/13/99 | 13-Dec-1999 | Month-Day order automatically switched |
"13/12/00" | 12/13/00 | 13-Dec-2000 | Month-Day order automatically switched; uses two digit year window |
"13/12/01" | 12/1/13 | 1-Dec-2013 | First number assumed to be year |
"13/12/02" | 12/2/13 | 2-Dec-2013 | First number assumed to be year |
"13/12/31" | 12/31/13 | 31-Dec-2013 | First number assumed to be year |
"13/12/32" | 12/13/32 | 13-Dec-2032 | Month-Day order automatically switched (similar to years 2000 and earlier) |
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.
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:
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:
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:
While these changes are nice, they are some very serious limitations to using these patches:
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.
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:
Function | Without Patch | With Patch |
CVDate(“1/1/00”) | 1/1/00 (1900) | 1/1/2000 (2000) should be 1/1/00 |
CVDate(“1/1/29”) | 1/1/29 (1929) | 1/1/2029 (2029) should be 1/1/29 |
CVDate(“1/1/1900”) | 1/1/00 (1900) | 1/1/00 (1900) should be 1/1/1900 |
CVDate(“1/1/1929”) | 1/1/29 (1929) | 1/1/29 (1929) should be 1/1/1929 |
CVDate(“1/1/2000”) | 1/1/2000 (2000) | 1/1/2000 (2000) should be 1/1/00 |
CVDate(“1/1/2029”) | 1/1/2029 (2029) | 1/1/2029 (2029) should be 1/1/29 |
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.
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.
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.
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.
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.
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.
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.
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.
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:
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.
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.
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.
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.
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:
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.
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
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)
Table Relationships
Queries
Forms
Reports
Macros
Modules
External File References
File Import/Export Specifications
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:
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:
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 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 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.
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.
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.
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:
These properties must be properly set to make sure Year 2000 issues are handled.
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:
Input Mask | Sample Input | Year 2000 Status |
99/99/00;0;_ | 1/1/99 12/31/00 |
Not Compliant |
99/99/0000;0;_ | 1/1/1999 12/31/2000 |
Compliant |
99\->L<LL\-00;0;_ | 01-Jan-99 31-Dec-00 |
Not Compliant |
99\->L<LL\-0000;0;_ | 01-Jan-1999 31-Dec-2000 |
Compliant |
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.
Format | Sample Display | Year 2000 Status |
Medium Date | 1-Jan-99 31-Dec-00 |
Not Compliant |
d-mmm-yyyy | 1-Jan-1999 31-Dec-2000 |
Compliant |
Short Date | 1/1/99 or 1/1/1999 12/31/00 or 12/31/2000 Varies with Control Panel settings |
Not Compliant |
m/d/yyyy | 1/1/1999 12/31/2000 |
Compliant |
mm/dd/yyyy | 01/01/1999 12/31/2000 |
Compliant |
Long Date | Friday, January 1, 1999 Lots of different formats based on Control Panel settings. Can be set to two-digit years by user. |
Not Compliant |
dddd, MMMM dd, yyyy | Friday, January 1, 1999 | Compliant |
General Date | 1/1/99 12:34 am 12/31/2000 12:34 pm Varies with Control Panel settings. Displays time if it exists. |
Not Compliant |
m/d/yyyy hh:nn:ss am/pm | 1/1/1999 12:34 am 12/31/2000 12:34 pm |
Compliant |
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:
Similarly, you should set the ValidationText property to provide helpful information to allow the user to identify why the rule failed.
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.
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!
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.
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.
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:
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.
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 20th 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.
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.
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:
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.
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.
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.
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".
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.
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.
For more information on Year 2000 issues in Access, please consult these resources:
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!
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.
Strategic Overview
Microsoft Access within an Organization's Database Strategy
How many simultaneous Microsoft Access users?
Blaming Microsoft Access instead of the Developer
Microsoft Access Version Feature Differences
Microsoft Access Versions, Service Packs and Updates
Microsoft Office 365 Access Update Version Releases
Top 14 Features Added with MS Access 2007
Taking Over Legacy MS Access Databases
Winner of Every Best Access Add-in Award
Set AutoNumber Starting Number Other than 1
Avoid Unnecessary or Duplicate Indexes
Copy Command Button and Keep Picture
Module VBA to Forms and Controls
Subform Reference to Control Rather than Field
Suppress Page Headers and Footers on the First Page of Your Report
Annual Monthly Crosstab Columns
Add Buttons to the Quick Access Toolbar
Collapse the Office Ribbon for more space
Avoid Exits in the Body of a Procedure
Send Emails with DoCmd.SendObject
Error Handling and Debugging Techniques
Error Number and Description Reference
Remote Desktop Connection Setup
Terminal Services and RemoteApp Deployment
Missing Package & Deployment Wizard
Remove 'Save to SharePoint Site' Prompt from an Access Database
Class Not Registered Run-time Error -2147221164
Microsoft Access to SQL Server Upsizing Center
When and How to Upsize Access to SQL Server
SQL Server Express Versions and Downloads
Deploying MS Access Linked to SQL Azure
SQL Server Azure Usage and DTU Limits