Access 2 and Access 97 date issues and Year 2000

Contents

Date representation

Access2 assumes that dates entered in 2 digit format in range 00-29 indicate 20th century , Access 97 assumes that dates entered in 2 digit format in range 00-29 indicate 21st century

Changing the date format on the Control Panel

Microsoft Access can display a wide range of international formats. To help ensure consistency among applications, Microsoft Access uses the regional settings from the Control Panel for the predefined Number and Date/T ime formats.

    Windows 3.1:

    • Select Control Panel from the Main window
    • Select International Settings
    • Click the Date Format Change button
    • Change Short and Long Date Format Order to be DMY
    • Short Format Century box should have a cross to show 4 digits
    • Long Format year arrow should be pressed to display 4 digits

    Windows NT:

    • Select Settings from the Start button
    • Select Control Panel
    • Select Regional Settings
    • Select the Date tab

Recommended settings:

Short Date Style: d/m/yyyy

Long Date Style: dddd, MMMM dd, yyyy

Description of the Regional date formats

  • For numbers with no leading zeros for single-digit days, years, or months, use lowercase d or y, or uppercase M.
  • For numbers with leading zeros for single-digit days, years, or months, use lowercase dd or yy, or uppercase MM.
  • For abbreviations for the day, year, or month, use lowercase ddd or yy, or uppercase MMM.
  • For the full name of the day, year, or month, use lowercase dddd or yyyy, or uppercase MMMM.
  • To add text, use single quotation marks (') around text.

Date Entry

If you enter 1/12/ into a date field, an error message is now returned. Use 1/12 instead and the current year will be entered by default. (At least 2 values for a date field must be given- thus 3/98 will be entered as 1 March 1998 providing the date f ormat is set up correctly from the control panel)

Queries with Date Criteria Between 1900 and 1929

If you use a literal date specifying the years 1900 through 1929 as part of your criteria in a Access 2.0 query, the query may return different results after you convert the database to Access 97. This happens b ecause new interpretation of year dates ending in the digits 00 through 29 places them in the next century.

You may even see different results from the same query if it is run on an enabled database.

For example, a literal date criteria of #01/01/15# in Access 2.0 represents January 1, 1915; the same date criteria in Access 97 represents January 1, 2015.

To work around this difference, modify the criteria to specify the correct century, for example #01/01/1915#.

One could also use the Access97 cdate function to force conversion to a date

Date and Time Formats in Access

You can set the Format property to predefined date and time formats or use custom formats for the Date/Time data type.

Predefined Formats

The following table shows the predefined Format property settings for the Date/Time data type.
Setting Description
General Date (Default) If the value is a date only, no time is displayed; if the value is a time only, no date is displayed. This setting is a combination of the Short Date and Long Time settings. Examples: 4/3/93, 05:34:00 PM, and 4/3/93 05:34:00 PM.
Long Date Same as the Long Date setting in the Regional Settings Properties dialog box in Windows Control Panel. Example: Saturday, April 3, 1993.

Medium Date

Example: 3-Apr-93.

Short Date

Same as the Short Date setting in the Regional Settings Properties dialog box in Windows Control Panel. Example: 4/3/93.Warning The Short Date setting assumes that dates between 1/1/00 and 12/31/29 are twenty-first century dates (that is, the years are assumed to be 2000 to 2029). Dates between 1/1/30 and 12/31/99 are assumed to be twentieth century dates (that is, the years are assumed to be 1930 to 1999).

Long Time

Same as the setting on the Time tab in the Regional Settings Properties dialog box in Windows Control Panel. Example: 5:34:23 PM.

Medium Time

Example: 5:34 PM.

Short Time

Example: 17:34.

Custom Formats

You can create custom date and time formats by using the following symbols. These formats are also used in the format function.

Symbol

Description

: (colon)

Time separator. Separators are set in the Regional Settings Properties dialog box in Windows Control Panel.

/

Date separator.

c

Same as the General Date predefined format.

d

Day of the month in one or two numeric digits, as needed (1 to 31).

dd

Day of the month in two numeric digits (01 to 31).

ddd

First three letters of the weekday (Sun to Sat).

dddd

Full name of the weekday (Sunday to Saturday).

ddddd

Same as the Short Date predefined format.

dddddd

Same as the Long Date predefined format.

w

Day of the week (1 to 7).

ww

Week of the year (1 to 53).

m

Month of the year in one or two numeric digits, as needed (1 to 12).

mm

Month of the year in two numeric digits (01 to 12).

mmm

First three letters of the month (Jan to Dec).

mmmm

Full name of the month (January to December).

q

Date displayed as the quarter of the year (1 to 4).

y

Number of the day of the year (1 to 366).

yy

Last two digits of the year (01 to 99).

yyyy

Full year (0100 to 9999).

h

Hour in one or two digits, as needed (0 to 23).

hh

Hour in two digits (00 to 23).

n

Minute in one or two digits, as needed (0 to 59).

nn

Minute in two digits (00 to 59).

s

Second in one or two digits, as needed (0 to 59).

ss

Second in two digits (00 to 59).

ttttt

Same as the Long Time predefined format.

AM/PM

Twelve-hour clock with the uppercase letters "AM" or "PM", as appropriate.

am/pm

Twelve-hour clock with the lowercase letters "am" or "pm", as appropriate.

A/P

Twelve-hour clock with the uppercase letter "A" or "P", as appropriate.

a/p

Twelve-hour clock with the lowercase letter "a" or "p", as appropriate.

AMPM

Twelve-hour clock with the appropriate morning/afternoon designator as defined in the Regional Settings Properties dialog box in Windows Control Panel.

Custom formats are displayed according to the settings specified in the Regional Settings Properties dialog box in Windows Control Panel. Custom formats inconsistent with the settings specified in the Regional Settings Propert ies dialog box are ignored.

Note If you want to add a comma or other separator to your custom format, enclose the separator in quotation marks like this: mmm d", "yyyy.

Date Storage

Date variables are stored as IEEE 64-bit (8-byte) floating-point numbers that represent dates ranging from 1 January 100 to 31 December 9999 and times from 0:00:00 to 23:59:59. Any recognizable literal date values can be assigned to Date variab les. Date literals must be enclosed within number signs (#), for example, #January 1, 1993# or #1 Jan 93#.

Date variables display dates according to the short date format recognized by your computer. Times display according to the time format (either 12-hour or 24-hour) recognized by your computer.

When other numeric types are converted to Date, values to the left of the decimal represent date information while values to the right of the decimal represent time. Midnight is 0 and midday is 0.5. Negative whole numbers represent dates before 30 Dece mber 1899.

Date Functions

Use the IsDate function to determine if date can be converted to a date or time. CDate recognizes date literals and time literals as well as some numbers that fall within the range of acceptable dates. When converting a number to a date, the wh ole number portion is converted to a date. Any fractional part of the number is converted to a time of day, starting at midnight.

CDate recognizes date formats according to the locale setting of your system. The correct order of day, month, and year may not be determined if it is provided in a format other than one of the recognized date settings. In addition, a long date format is not recognized if it also contains the day-of-the-week string.

Other functions that may be useful are:

Format

Specify format of date value using above formats eg format(date_field,"ddd")

Year

Year part of date value 100 to 9999

Month

Month part of date value 1 to 12

Weekday

Weekday part of date value 1 (Sunday) to 7

Day

Day of month of date value 1 to 31

Hour

Hour part of date value 0 to 23

Minute

Minute part of date value 0 to 59

Second

Second part of date value 0 to 59

Time()

Current time

Date()

Today not including time

Now()

Today including time

Datediff

Subtract two dates

Dateadd

Add two dates

Visual Basic

Access 97 Visual Basic treats dates inconsistently, ignoring the regional settings if a literal date makes sense in American format. For example, with a British date setting (dd/mm/yy) in Control Panel, enter these two calculations in the debug window:

? #23/12/96# - #20/12/96#

? #13/12/96# - #10/12/96#

The first yields 3 days, but the second Visual Basic interprets #13/12/96# as 13-Dec-96, but #10/12/96# as 12-Oct-96. Access Basic (ver. 1, 2) always respected the Control Panel setting and did not have this inconsistency. The solution is to state the month explicitly eg #13 dec 96#. This problem only applies when using Visual Basic not to a query.

Form/Report Date/Time controls

It is now easier to add controls to display page numbers and dates since location and format are asked for

You will probably have to change the page/ date control colour since it seems to take characteristics of the text fields rather than the header fields