Harvey's Almanac of Microsoft Access Engineering

Y2k

The Y2K problem persists beyond Year 2000

Two-digit Years should not be used anywhere

No application should have features that are not Y2K compliant.

The British Standards Definition of Year 2000 compliance (DISC PD2000-1 A Definition of Year 2000 Conformity Requirements) is as follows :-

	Rule 1: General Integrity. No value for current date will cause interruption in operation
	Rule 2: Date integrity. Date-based functionality must behave consistently for dates prior to, during and after year 2000.
	Rule 3: Explicit/Implicit century. In all interfaces and data storage, the century in any date must be specified either explicitly or by unambiguous algorithms or inferencing rules.
	Rule 4: Year 2000 must be recognised as a leap year.

These are the rules that current and future MS Access applications should follow and explicitly, for dates between years 100 to 9999. The term "Year 2000 compliant", used in this web, refers to compliance to all of the rules used above.

There are four main Classes of Year 2000 problems for MS Access.

	Access Projects
	Databases and Files
	Data
	Executables and Other Programs

Access Projects

Access Projects contain code, objects and data. They can be the user application, a library application or just a container for tables.

Databases and Files

Databases can be simple or complex containers, such as Jet databases, MS SQL Server and Oracle, which could contain many other processes beyond tables. Files can be simple, such as CSV files, or complex such as formatted Excel files with macros.

Data

Data can be in tables in the application, attached data, fetched data or computed data. Data, or the systems that generate it, may not be Year 2000 compliant.

Executables and other Programs

There are many programs required to make a MS Access application run.

The MS Access interactive or runtime environment has to call many executables. These differ with MS Access revision. This issue to be partly reduced by running only one version of Access. It must be noted that there are different versions of the file OLEAUT32.DLL that give different results for certain date formats. This must be understood and policed by the System Administrator, so that only one version exists for the group of users.

Object linking and embedding to other applications, such as Word or Excel, and even the Operating System file structure, can also be an issue. The compliant date limits of such applications and versions vary. EG Project - 2049, Excel - 2078, Windows file system (FAT32) - 2099, Visual C++ runtime library - 2036, etc.

Date Literals

Date literals are one of the most important and most common Year 2000 compliance issues in MS Access applications. A date literal is a short string of text that can be identified as a date, date/time or time, by enclosing it within a pair of hash symbols "#", such as "#12-Dec-1990#".

These are treated differently by :

	Different versions of MS Access
	Different objects within the same version of MS Access
	Which century is being described
	What Regional Settings are in use

Try and use the DateSerial() function instead.

The International aspects need to be considered. The version of Windows 2000, most commonly available in the UK, has 58 different geographic regions for Date/Time settings, from Afrikaans to Swedish.

Short Date format should not be used anywhere in the application, as this is controlled by Regional Settings.

The places that these date literals may be used include :

Tables

Table Properties

ValidationRule

Field Properties

	DefaultValue
	ValidationRule

Queries

	Criteria
	Variables Passed to Functions

Forms

RecordSource

Controls

	DefaultValue
	ControlSource

Reports

RecordSource

Controls

	DefaultValue
	ControlSource

Macros

	ApplyFilter - WhereCondition
	FindRecord - FindWhat
	OpenForm - WhereCondition
	OpenReport - WhereCondition
	RunCode - FunctionName (Passed Variable)
	RunSQL - SQLStatement
	SetValue - Expression

Modules

	Functions
	Subs
	Declarations

Date Functions

Date Functions are a common place to find instances of non-compliance. Such functions can be found in all object types.

These functions include :

	CDate Function
	Date Function
	DateAdd Function
	DateDiff Function
	DateSerial Function
	DateValue Function
	Day Function
	FileDate Time Function
	Format Function
	IsDate Function
	Minute Function
	Month Function
	Now Function
	Second Function
	Weekday Function
	Year Function

International Ambiguity

International date ambiguity is a serious issue. The date 1/2/2003 is interpreted differently between the USA and UK.

One-digit or two-digit months should not be displayed.

Month names should be shown as characters. Abbreviated months should be shown as three-character months. Where possible, month names, whether abbreviated or not, should be displayed in the local language.