XL: Access ODBC Driver Returns Date of 12/30/1899 (125849)
The information in this article applies to:
- Microsoft Excel for Windows 95 7.0a
- Microsoft Excel for Windows NT 5.0
- Microsoft Excel for Windows 5.0c
This article was previously published under Q125849 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:
- Add the field to the data pane.
- Double-click the field heading in the data pane.
- In the Field box, type +2
after the field name.
- 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:
- Click the SQL button.
- Find the section that reads "<fieldname>+2".
- 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:
- In Microsoft Excel, select the data to convert to valid dates.
- On the Edit menu, click the Replace command.
- 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.
Modification Type: | Minor | Last Reviewed: | 8/19/2003 |
---|
Keywords: | KB125849 |
---|
|