ACC: Output to XLS Changes Text to Scientific Notation Number

• Microsoft Access versions 2.0, 97

SYMPTOMS

Novice: Requires knowledge of the user interface on single-user computers.

When you output a Microsoft Access report to Microsoft Excel file format, a text string consisting of both alphabetic 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 MSExcel button while 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. The problem can occur with a text string containing the letter "e" in a Microsoft Access 2.0 report, or with a text string containing the letter "d" or the letter "e" in a Microsoft Access 97 report.

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 Save As/Export on the File menu, and then choosing To An External File or Database. 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 2.0

1. On the File menu, click Export.

2. In the Data Destination list of the Export dialog box, click Microsoft Excel 2.0-4.0 or Microsoft Excel 5.0. Then click OK.

3. In the Select Microsoft Access Object dialog box, click the object that you want to export, and click OK.

4. In the Export To File dialog box, choose a directory and type a name in the File Name box. Then click OK.

In Microsoft Access 97

1. In the Database window, select the table or query that you want to export.

2. On the File menu, click Save As/Export.

3. In the Save As dialog box, click "To an External File or Database," and then click OK.

4. In the "Save <objectname> In" dialog box, select the folder to which you want to export the file.

5. From the Save As Type list, select the version of Excel to which you want to export the file.

6. Type a name in the File Name box, and then click Export.

STATUS

Microsoft is researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.

MORE INFORMATION

This problem 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 named tblToXLS:

         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. If you are using Microsoft Access 97, click the Reports tab in the Database window, 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.

   If you are using Microsoft Access 2.0, click the Report tab in the Database window, and then click New; in the New Report dialog box, select tblToXls from the Select A Table/Query list and click the Report Wizards button. In the Report Wizards dialog box, click AutoReport, and then click OK.

5. While previewing the report, click the Analyze It With Excel button on the toolbar. If you are using Microsoft Access 97, click the OfficeLinks button on the toolbar, and then click the Analyze It with Excel button.

   If you are using Microsoft Access 97, note that 20d4 and 20e070 appear in Microsoft Excel as 200000 and 2E+71 respectively. If you are using Microsoft Access 2.0, 20e070 appears in Microsoft Excel as 2E+71.