Microsoft KB Archive/210423

= ACC2000: Rounding Errors When You Use Floating-Point Numbers =

Article ID: 210423

Article Last Modified on 6/24/2004

-

APPLIES TO


 * Microsoft Access 2000 Standard Edition

-



This article was previously published under Q210423



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 calculate by using floating-point numbers, the result is not always what you expect.



CAUSE
Errors similar to the example in the "More Information" section of this article occur in any programming language that uses floating-point numbers. This is because decimal fractions do not always have exact binary equivalents, which can result in rounding errors.



RESOLUTION
There are several methods that you can use to avoid rounding errors when you are using floating-point numbers:  Use the Decimal, Numeric, Currency, Money or SmallMoney data type. These data types are stored as scaled integers instead of floating point numbers, which prevents rounding errors from occurring.

NOTE: The monetary data types (Currency, Money and SmallMoney) are accurate to four decimal places or fewer. The accuracy of the Decimal and Numeric data types depend on the Percision and Scale settings. If you are using Single numbers, convert them to Doubles and use formatting to hide the extra digits. If there are any rounding errors, they will occur at the end of the numbers where they will not affect the visible data.  Place the CDec function around each component of the expression that could possibly contain decimal data. It is not necessary to use CDec around components of an expression that are guaranteed to be whole numbers. x = CDec(4.555) * 100   Break the calculation into two or more steps. When you list a calculation in one long line, Visual Basic stores the intermediate values internally. For example, in the calculation CInt(4.555 * 100) the value 455.5 is temporarily stored and used in the CInt function. If you break the calculation into the two steps x = 4.555 * 100 CInt(x) you avoid this internal storage, and therefore avoid the floating-point rounding error. 



Steps to Reproduce the Behavior
The following steps demonstrate the way floating point calculations may affect the final result: <ol> Start Microsoft Access, and then open any database.</li> Press ALT+F11 to open the Visual Basic Editor.</li> Press CTRL+G to open the Immediate window.</li> Enter the following line, and then press ENTER:

Print CInt(3.555 * 100)

Note that the correct result of 356 is returned.

</li> Enter the following line, and then press ENTER:

Print CInt(4.555 * 100)

This calculation returns 455 instead or 456.</li></ol>

<div class="references_section">