ACC2002: Import Spreadsheet Wizard Does Not Preserve Long or Medium Date Formats (282365)


The information in this article applies to:

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

This article applies only to a Microsoft Access database (.mdb).

SYMPTOMS

When you import data from an Excel spreadsheet into an Access database, dates in long date format, such as January 1, 2000; or medium date format, such as 1-Jan-2000, lose formatting and are displayed as short dates, such as 01-01-2000.

RESOLUTION

To resolve this issue, reformat the date fields after you have imported the data. To dot his, follow these steps:
  1. Open the table in Design View.
  2. Click the date field you want to format.
  3. Change the Format property to either Medium Date or Long Date.
  4. Save the table. On the View menu, click Datasheet View.
The date field now displays the new formatting that you selected.

STATUS

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

MORE INFORMATION

Steps to Reproduce Behavior

  1. Start Microsoft Excel and create a new workbook.
  2. In cell A1, enter 1-Jan-00.
  3. Save the file as TestDate and quit Excel.
  4. Start Microsoft Access and create a new database.
  5. On the File menu, point to Get External Data and click Import.
  6. Navigate to the TestDate file created in step 3 and click Import.
  7. In the Import Spreadsheet Wizard, click Finish.
  8. Open the newly created Sheet1.

    The date is displayed as 1/1/2000.

REFERENCES

For more information about formatting date fields, click Microsoft Access Help on the Help menu, type Format Property - Date/Time Data Type in the Office Assistant or the Answer Wizard, and then click Search to view the topic.
Modification Type:MajorLast Reviewed:11/6/2003
Keywords:kbbug kbpending KB282365
©2003 Microsoft Corporation. All rights reserved.