ACC2002: Default Value for Datetime Column Appears as 12/30/1899 (287490)

Microsoft Access 2002

This article was previously published under Q287490

Novice: Requires knowledge of the user interface on single-user computers.

This article applies only to a Microsoft Access project (.adp).

SYMPTOMS

When you create a table that includes a datetime column, and you type a DefaultValue property for that column, the default value may be displayed as 12/30/1899 instead of the value that you typed.

CAUSE

This behavior occurs if the DefaultValue property is not enclosed in apostrophes.

RESOLUTION

You must enclose DefaultValue properties for datetime columns in apostrophes, for example, '1/1/2001'. This rule does not apply when you use functions such as getdate() for a default value.

STATUS

Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.

MORE INFORMATION

If the DefaultValue property is presented without apostrophes, Microsoft SQL Server attempts to calculate the value as an expression. As a result, a date value such as 1/1/2001 is evaluated through division and returns a value near zero (0). When the value zero is formatted as a date, it is displayed as 12/30/1899.

Steps to Reproduce the Problem

Open the sample project NorthwindCS.adp.
Under Objects, click Tables, and then double-click Create table in Design view.

Create two columns with the following properties:

   Table: Dates
   ----------------------------
   Column Name: DateID
   Data Type: bigint
   Length: 8
   Allow Nulls: no
   Default Value: 1/1/2001

   Column Name: TestDate
   Data Type: datetime
   Length: 8
   Allow Nulls: yes

   Table Properties: Dates
   -------------------------------
   PrimaryKey: DateID

Save the table by the name "Dates."
Open the table in Datasheet view.

Notice that the default date is 12/30/1899 rather than 1/1/2001 as expected.

Modification Type:

Major

Last Reviewed:

11/5/2003

Keywords:

kbbug kbnofix KB287490