Year 2000 Issues in Microsoft Access
By Luke Chung, President, FMS, Inc.
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
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
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
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
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
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
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
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.
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 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:
- Every machineís clock is different.
- 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.
- 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 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
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.
Even if you understand the data entry rules
for two-digit years described for each version of Access, there are
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:
ending in 00
ending in 29
any year ending in 99
The odd results are:
- 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.
- 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.
- 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
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.
automatically switched; uses two digit year window
||First number assumed to
||First number assumed to
||First number assumed to
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:
Version 1, May 7,
Version 2, September
Version 3, November
Version 4, December
The latest patch and
information on previous patches is available for free from the Microsoft
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
information applies to the originally released patch in May that has since
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)
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
About the Patch
The patch is fairly small
(255K) and includes three files:
MSABC200.DLL: an updated Access Basic file.
MSAJU200.DLL: an updated Access Jet Utilities file.
Readme.txt: installation information and the End User Licensing
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
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:
(2000) should be 1/1/00
(2029) should be 1/1/29
(1900) should be 1/1/1900
(1929) should be 1/1/1929
(2000) should be 1/1/00
(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.
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
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.
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
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
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:
- Analyze the objects in your database and
related objects for Year 2000 and date related issues.
- Prioritize or assign a risk level to each
- Determine which issues will be fixed
- Determine the fixes (solutions) to
- Implement the fixes and test them
- Certify individual objects are Year 2000
- Test the entire application after all the
fixes are applied
- Certify the database is Year 2000
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:
- Find Date/Time Fields
- Determine Non-Date/Time Fields that
contain date data:
Imported text fields (e.g. mainframe
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
- 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.
- Determine if date fields in indexes will
cascade or delete data if bad data is entered.
- 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
- Verify how dates are used in the record
- 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
- Verify how dates are used in the record
- Verify the date controls are wide enough
to display four-digit years without truncation or word-wrapping
- Detect use of date related data or
- Detect dates used in the Condition clause
- Donít use TransferText command
- Detect any use of date related Access
- 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,
- Determine the ActiveX controls and DLLs
used to verify date usage
- Verify usage of library code in VBA
- 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.
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:
Diagnosing and Fixing Object Property
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
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.
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
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.
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
- 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
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:
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
12/31/00 or 12/31/2000
Varies with Control Panel settings
Lots of different formats based on Control Panel settings. Can be set to
two-digit years by user.
|dddd, MMMM dd,
||1/1/99 12:34 am
12/31/2000 12:34 pm
Varies with Control Panel settings. Displays time if it exists.
12/31/2000 12:34 pm
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.
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
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
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
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
Review Programmatic (Custom) Import/Export
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
- 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#
In Access 2.0, the resulting file looks like
this (no 20th
In Access 97, the resulting file looks like
this (centuries are omitted for years between 1930 and 1999):
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")
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
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.
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
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.
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
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
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
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
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:
- Papers on Year 2000 issues in Access and
Office from FMS:
- Year 2000 product for Access: Total Access Inspector 2000
This product identifies Year 2000 problems and date usage in your
- Microsoft Year 2000 web site:
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.
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!
Copyright © 1998-1999, FMS Inc. All rights
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.