Rounding error?

  • Thread starter Thread starter Jeroen Verdrengh
  • Start date Start date
J

Jeroen Verdrengh

Hi,

I've put a calculated field in a query:

Sum([Stock]![Price_Excl_TVA]*(1+[Catalog_TVA]![Percentage]))

where Stock and Catalog_TVA are tables and
Price_Excl_TVA: Valuta
Percentage: Numeric, single precision

When I execute the query with values as below I get this:
120*(1 + 0.1) = 132,000000178814
100*(1 + 0.21) = 120,999999344349

What could I have done wrong?

greets,

Jeroen
 
You used a floating-point (approximate) data-type -- single (or double) --
instead of a fixed-precision (decimal) type. It's not just Access... it's
the nature of floating point numbers. Search sqlserver.programming and
you'll see many examples of this confusion. Whether by design or luck,
Oracle avoided this by using the NUMBER (numeric/decimal) datatype for all
numeric data.

My rule of thumb is to avoid single/double/float/real like the plague where
accuracy is vital, such as financial data where it has to reconcile to the
penny. I have trouble finding cases where I can justify floating point
types... maybe statistical data where the error tends to dwarf the decimal
places we can calculate. I still hear my high-school math & science
teachers stressing the need to adhere to least significant digits rules in
such calculations to eliminate false precision -- round to the # of decimal
places in the least-precise #.

This misunderstanding about floating-point numbers is one of the most common
sources of programming error & frustration.
Some explanations of floating math...
http://www.math.grin.edu/~stone/courses/fundamentals/IEEE-reals.html
http://www.physics.ohio-state.edu/~dws/grouplinks/floating_point_math.pdf
http://support.microsoft.com/default.aspx?scid=kb;EN-US;42980
http://support.microsoft.com/?kbid=165373
http://support.microsoft.com/default.aspx?scid=kb;EN-US;69333
http://support.microsoft.com/default.aspx?scid=kb;en-us;111781&Product=acc97
http://www.fmsinc.com/tpapers/math/
http://www.aspfaq.com/show.asp?id=2477
 
Back
Top