Microsoft KB Archive/207857

= ACC2000: Excel ISAM Rounding Errors =

Article ID: 207857

Article Last Modified on 3/10/2003

-

APPLIES TO


 * Microsoft Access 2000 Standard Edition

-



This article was previously published under Q207857



Moderate: Requires basic macro, coding, and interoperability skills.

This article applies to a Microsoft Access database (.mdb) and to a Microsoft Access project (.adp).



SYMPTOMS
When you export data to Microsoft Excel 2000, numeric values are displayed in a more precise way than they were in Microsoft Access, making the values appear to be incorrect. For example .15 in Microsoft Access appears as 0.150000006 when exported to a Microsoft Excel worksheet.



CAUSE
Microsoft Excel supports a different level of precision than does Microsoft Access. This difference in rounding behavior occurs because of floating point conversion.



RESOLUTION
The following are some methods to work around the different level of precision. These are not meant to be all-inclusive.
 * In an unbound text box on a form or report, you can set the Format property to "0.0".
 * In other controls, you can use a combination of the Format and Val functions to convert the calculated value to the same precision as the displayed value.
 * You can change the formatting of the cells in the Microsoft Excel spreadsheet.



Steps to Reproduce Behavior

 * 1) Start Microsoft Access and open the sample database Northwind.mdb.
 * 2) In the Database window, click Tables, and then select the Order Details table.
 * 3) On the File menu, click Export.
 * 4) In the Export Table 'Order Details' To dialog box, in the Save as type box, select Microsoft Excel 97-2000 (*.xls). In the File name box, type RoundImport.xls, and then click Save.
 * 5) In the Database window, click Tables, and then click New.
 * 6) In the New Table dialog box, click Link Table, and then click OK.
 * 7) In the Link dialog box, in the Files of type text box, select Microsoft Excel (.xls). In the list of files, click RoundImport.xls, and then click Link to create a linked table.
 * 8) In the Link Spreadsheet Wizard, click Next twice, and then, in the Linked Table Name text box, type RoundImport . Click Finish.
 * 9) In the Database window, open the new linked RoundImport table in Datasheet view. Note that some of the values in the Discount field now contain incorrect values. For example, 20% is now 0.200000002980232.

Additional query words: prb

Keywords: kbinterop kbprb KB207857

-

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

© Microsoft Corporation. All rights reserved.