unexpected exponential results on a sum field

  • Thread starter Thread starter karen scheu via AccessMonster.com
  • Start date Start date
K

karen scheu via AccessMonster.com

I have a select query with totals as folllows: group on order number and sum
three fields. See below for detail rows.

order quantity qtyship EXTPRICE
12345 -1 -1 -312.4
12345 -1 -1 -384
12345 -1 -1 -528
12345 1 1 312.4
12345 1 1 384
12345 1 1 528


My problem is that when I group by order and sum on the three fields above,
the extprice field comes back as .13686837721616E-13. Does anyone know what
is going on? The sum should net out to 0.

Thanks,
Karen
 
Things like that happen with floating-point arithmetic. You can use the
function round to get around this problem (pun intended). For example,
round(x,2) rounds x to two decimal places.

/Daniel
 
I have a select query with totals as folllows: group on order number and sum
three fields. See below for detail rows.

order quantity qtyship EXTPRICE
12345 -1 -1 -312.4
12345 -1 -1 -384
12345 -1 -1 -528
12345 1 1 312.4
12345 1 1 384
12345 1 1 528


My problem is that when I group by order and sum on the three fields above,
the extprice field comes back as .13686837721616E-13. Does anyone know what
is going on? The sum should net out to 0.

Thanks,
Karen
Click to expand...

Double Float numbers are *approximations*, accurate to some 14 decimal
places; just as the numbers 1/3 and 1/7 cannot be represented exactly
as a decimal number, so numbers like 312.4 cannot be represented
exactly as a binary fraction in a Double. You're getting the typical
roundoff error caused by this.

I'd suggest using a Currency field type for the Extprice. Currency
(the currency datatype, not just a currency format) is a scaled huge
integer with exactly four decimal places, and does not suffer this
type of roundoff error.

John W. Vinson[MVP]
 
Back
Top