Microsoft KB Archive/202205

= ACC2000: Output to XLS Changes Text to Scientific Notation Number =

Article ID: 202205

Article Last Modified on 11/29/2000

-

APPLIES TO


 * Microsoft Access 2000 Standard Edition

-



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  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
 Start Microsoft Access, and open a new blank database.  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.   Open the tblToXLS table in Datasheet view, and enter the following data:   TextTest 20b070 20c070 20d4 20e070 20f070 Close the table.  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.</li> Preview the report, click the arrow to the right of the OfficeLinks button on the toolbar, and then click Analyze It with MS Excel.</li></ol>

Note that 20d4 and 20e070 appear in Microsoft Excel as 200000 and 2E+71 respectively.

Additional query words: save export exponential prb

Keywords: kbnofix kbprb KB202205

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.