ACC: Output to XLS Changes Text to Scientific Notation Number

ID: Q180590

The information in this article applies to:

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 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 MSExcel 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. 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 has confirmed this to be a problem in the Microsoft products listed at the beginning of this article.


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:
    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.


Additional query words: save export exponential 


Keywords          : IntpOthr OtpExl 
Version           : WINDOWS:2.0,97
Platform          : WINDOWS 
Issue type        : kbbug

Last Reviewed: May 4, 1999