Microsoft KB Archive/180590

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

Article ID: 180590

Article Last Modified on 1/22/2007

-

APPLIES TO


 * Microsoft Access 2.0 Standard Edition
 * Microsoft Access 97 Standard Edition

-



This article was previously published under Q180590



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



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 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 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 that this is a problem in the Microsoft products that are 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
 Start Microsoft Access, and open a new blank database. Create the following table:

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.  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.</li> 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.</li></ol>

Additional query words: save export exponential

Keywords: kbbug kbnofix KB180590

-

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

© Microsoft Corporation. All rights reserved.