ACC2000: Output to XLS Changes Text to Scientific Notation Number

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.

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.

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.

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.