Help with SUM query that has negative sign value

[eerich]

Hello,

I have a query that is returning incorrect values with trying to sum an amount column. Basically I have values both positive and negative in the fields. When I execute the query to sum the values it returns the wrong value.

For instance:
Amount 12398.60
Amount -258.80
Amount -12139.80

These values should sum to zero; however Access returns the value as 1.81898940354586E-12

Any ideas what is going wrong?

My sql query is
Select sum(amount)
from mytable;

Any help is appreciated

 

[RoyVidar]

When dealing with float numbers (single, double), it will often store/present the closest representation of a number not being exactly number that number.

The number you get is very close to 0, but not exactly 0.

Depending on the precision you need, either format it with 0 or two decimals, or try altering the datatype of the field to for instance Decimal. If so, be sure to remember to set scale (number of digits to the right of the decimal separator)

Roy-Vidar

 

[eerich]

Thanks - that solved the issue. I converted the field to a currency field with 5 decimal places. I hope this doesn't alter the integrity of the data.