XL: Access ODBC Driver Returns Date of 12/30/1899

ID: Q125849

The information in this article applies to:

Microsoft Excel for Windows, versions 5.0, 5.0c
Microsoft Excel for Windows NT, version 5.0
Microsoft Excel for Windows 95, versions 7.0, 7.0a

SYMPTOMS

In Microsoft Excel, when you retrieve Microsoft Access data from Microsoft Query, if a date/time field contains a time without a date, the data in that field will be returned with a date of 12/30/1899. This presents a problem when the data is returned to Microsoft Excel because Microsoft Excel does not recognize dates earlier than 1900. Microsoft Excel interprets the data as text rather than Date/Time values, and you cannot successfully apply a date or time number format to the data in Microsoft Excel.

CAUSE

These results are by design. When you create a time or date format in Microsoft Access, the program internally stores both the date and time. However, Microsoft Access masks the date or the time depending on the format you choose. So, in a Time format, it must store some date. The date it uses as a "dummy" date is '1899-12-30'.

WORKAROUND

To work around this problem, use either of the following methods.

Method 1: In Microsoft Query, change the date from 12/30/1899 to 1/1/1900,

          which is a valid date in Microsoft Excel.

          To change the field definition in MSQuery to an expression that
          is the field name + 2, use the following steps:

          1. Add the field to the data pane.

          2. Double-click the field heading in the data pane.

          3. In the Field box, type "+2" (without the quotation marks)
             after the field name.

          4. Click OK.

          When field names are returned to Microsoft Excel, the expression
          "<fieldname>+2" or "Expr1001" may be displayed. To remedy this,
          do the following in MS Query:

          1. Click the SQL button.

          2. Find the section that reads "<fieldname>+2".

          3. After the 2, enter the following text

               " As <alias> " (note leading and trailing space)

          where <alias> is what you want to appear as a field name when
          data is returned to Microsoft Excel.

Method 2: In Microsoft Excel, convert the values to valid Date/Time values

          once the data has been retrieved to Microsoft Excel. For example,
          you could do the following to convert the returned values to
          dates by using the Replace command to replace the date 12/30/1899
          with a valid date:

          1. In Microsoft Excel, select the data to convert to valid dates.

          2. On the Edit menu, click the Replace command.

          3. In the Find What box, type "1899-12-30" and in the Replace
             With box, type "1994-12-30," and then choose the Replace All
             button.

Additional query words: 5.00 5.00c MSQuery 7.00 XL5 XL7

Keywords          : xlquery 
Version           : 5.00 5.00c 7.00 7.00a
Platform          : WINDOWS

Last Reviewed: March 27, 1998