ACC2002: Date Is Displayed as a Number When You Import an Open Excel Workbook (275069)


The information in this article applies to:

  • Microsoft Access 2002
This article was previously published under Q275069
Novice: Requires knowledge of the user interface on single-user computers.

This article applies to a Microsoft Access database (.mdb) and to a Microsoft Access project (.adp).

SYMPTOMS

When you use the Import wizard to import and open Excel workbook, Date fields are displayed as numeric values.

CAUSE

Typically, the Import wizard uses the Excel ISAM driver to retrieve data from an Excel workbook. However, if the Excel file is open and not shared, the Microsoft Jet database engine uses another method to retrieve the data. This other method produces this behavior.

RESOLUTION

To make sure that dates are displayed properly when you import an Excel file into Access, either close the Excel file, or permit the file to be shared. You can share the file in Excel 2002 as follows:
  1. In Excel, open the file that you want to share.
  2. On the Tools menu, click Share Workbook.
  3. In the Share Workbook dialog box, click the Editing tab, and then click the Allow changes by more than one user at the same time check box.

MORE INFORMATION

Steps to Reproduce the Behavior

  1. Open the Northwind sample database, and then export the Employees table as an Excel file named Employees.xls.
  2. Open the Employees.xls file in Excel. Note that both the BirthDate and the HireDate fields display date values.
  3. Open an Access database file.
  4. On the File menu, point to Get External Data, and then click Import to start the Import wizard.
  5. In the Import Wizard dialog box, select Microsoft Excel(*.xls) in the Files of Type box, select the Employees.xls file, and then click Import.
  6. Scroll through the sample data to the right. Note that both the BirthDate and the HireDate fields display numeric data.
Modification Type:MajorLast Reviewed:9/27/2003
Keywords:kbnofix kbprb KB275069
©2003 Microsoft Corporation. All rights reserved.