Floating Point Numbers and Scaling
Floating Point Numbers
In digital computers, floating point numbers offer a method of representing very large and very small numbers that are beyond the capacity of the processor to represent with absolute accuracy.
There may be an error when computing with floating point numbers. That occurs when there are no exact binary equivalents for all the numbers or for the result of the calculation.
The addition, subtraction, multiplication and division of two floating point numbers result in a floating point number. Such calculations may well result in error.
The Single and Double precision data types in Access store numbers as floating point numbers.
Therefore, in an Access query, form, or report, any expression drawing values from fields with these data types will be subject to error.
VBA supports the Single and Double precision data types for floating point numbers.
So any VBA functions you write to work with floating point numbers are subject to error too.
To illustrate error in a floating point calculation, use the Immediate Window in a Visual Basic Editor and input the expression ?1.001-1
The numbers are treated as floating point numbers. We expect the Window to return something like 9.9999999999989E-04. Clearly not the correct 0.001.
Scaling is the adjustment of a number so that it can be processed accurately by your computer.
The Access and VBA Currency data type scales numbers. Currency has its limits, however, because it will not store numbers beyond the fourth place of decimals.
So, invaluable as it may be for making accurate calculations, you can rely on error-free calculation only if the values used and the true results do not extend beyond this four decimal place limit.
In Access and VBA, you have the coercive CCur() function to perform scaling on any number (including floating point) you supply.
CCur() has the same four-decimal-place limit as the Currency data type.
VBA has the CDec() scaling function. It has limits but these are vastly wider than those applying to CCur().
To test the effect of applying scaling, try
?CDec(1.001)-1 and ?CCur(1.001)-1 in the Immediate window. Both should yield the correct result.
?CCur(1.00001)-1 fails, as CCur() chops the 1.00001 off early at its four decimal place limit.
?CDec(1.00001)-1 does the business.
Note that you must scale the numbers before the calculation. Scale its result, as in ?CDec(1.001-1), and you will suffer floating point errors.
If you attempt a custom user-defined rounding function, to, say mirror Excel’s ROUND(), you will be up against floating point errors.
Click here for our attempt at an Access-VBA, Excel-type ROUND().