ACC2000: Output to XLS Changes Text to Scientific Notation Number (202205)


The information in this article applies to:

  • Microsoft Access 2000
This article was previously published under Q202205
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 output a Microsoft Access report to Microsoft Excel file format, a text string that is made up of both alphabetical and numeric characters may be changed to a number, and this number may appear in scientific notation when you open the spreadsheet file in Microsoft Excel. You may also see this behavior when you click the Analyze It with MS Excel button while you are previewing a report.

CAUSE

In some cases, Microsoft Access may recognize a text string as a hexadecimal number when it outputs a report to Microsoft Excel (*.xls) file format.

RESOLUTION

Note that you may also see this behavior when you try to save a report as a spreadsheet file by opening it or selecting it in the Database window, clicking Export on the File menu, and then clicking Excel type, and typing the name before saving. Because this problem occurs whenever you try to save a report as a spreadsheet file, you should export the underlying table or query to Microsoft Excel if possible. To export a table or query to Microsoft Excel, follow these steps.

In Microsoft Access

  1. In the Database window, select the table or query that you want to export.
  2. On the File menu, click Export.
  3. In the Export <object> <objectname> To dialog box, select the folder to which you want to export the file.
  4. In the Save as type list, select the version of Excel to which you want to export the file.
  5. Type a name in the File name box, and then click Save.

MORE INFORMATION

This behavior occurs regardless of the version of Microsoft Excel that you are using.

Steps to Reproduce Behavior

  1. Start Microsoft Access, and open a new blank database.
  2. Create the following table: 
       Table: tblToXLS
   --------------------
   Field Name: TextTest
      Data Type: Text
      Indexed: No
    Save the table as tblToXLS. When prompted to create a primary key, click No.
  3. Open the tblToXLS table in Datasheet view, and enter the following data: 
       TextTest
   --------
   20b070
   20c070
   20d4
   20e070
   20f070
    Close the table.
  4. In the Database window, click Reports under Objects, and then click New; in the New Report dialog box, click Auto Report: Columnar, select tblToXLS as the table or query where the object's data comes from, and then click OK.
  5. Preview the report, click the arrow to the right of the OfficeLinks button on the toolbar, and then click Analyze It with MS Excel.
Note that 20d4 and 20e070 appear in Microsoft Excel as 200000 and 2E+71 respectively.
Modification Type:MajorLast Reviewed:11/29/2000
Keywords:kbprb KB202205
©2002 Microsoft Corporation. All rights reserved.