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

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 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.