ACC97: Excel ISAM Rounding Errors

 Last reviewed: June 4, 1997
Article ID: Q162821
The information in this article applies to:
  • Microsoft Access 97

SYMPTOMS

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

When you export data to Microsoft Excel for Windows 95 or Microsoft Excel 97, numeric values are displayed in a more precise way than they were in Microsoft Access, making the values appear to be incorrect. For example a .15 in Microsoft Access appears as .15000000596 when exported to a Microsoft Excel spreadsheet.

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 object, 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.

MORE INFORMATION

Steps to Reproduce Behavior

  1. Open the sample database Northwind.mdb.

  2. Export the Order Details table to Excel 97 format using Save As/Export command on the File menu.

  3. Create a linked table to this file and open it in Datasheet view.

    Note that some of the values in the Discount field now contain incorrect values. For example, 0.2 is now 0.200000002980232.

Keywords : IsmOthr kbinterop
Version : 97
Platform : WINDOWS
Hardware : X86
Issue type : kbprb
Resolution Type : kbworkaround

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: June 4, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.