Access 2003 summation error

[DanG]

Hi all

I'm getting a weird anomaly when I use the summation function of
Access. For example, when I sum the following numbers:

274.28
68.57
34.71
1022.4
25.38
-1425.34

I should get 0 (zero). Instead, I get 2.27373675443232E-13.

If I change the 274.38 to 274.36, I should get -.02, but instead get
-0.019999999. which is almost -.02. Makes me think of the old Intel
processor errors.

The data originally came from a Firebird database, which was being
summed and printed on a Crystal report via ODBC. The value should have
been zero, and apparently is really, really close to zero, but isn't
quite. A zero prints, though I have zero-suppress on, and that was my
clue. So even Crystal is not summing to exactly zero.

That's when I imported (not linked) the data into Access 2003, and ran
the summation there, confirming that the numbers are not summing
correctly.

Rounding to two decimals does not fix the error.

So I question my math processor. I have a Compaq nx7000.

Thoughts and suggestions are welcome.

Cheers
Dan

On a clear disk, you can seek forever.

 

[Garret]

I think that your variable you store your answer in is out of the scope
of your calculations (decimals or boolean value). Try changing these
to Doubles and try again.

 

[DanG]

Thanks for the reply, Garret.

Within Access, I'm only running a SELECT id, sum(amount) GROUP BY id.
There isn't any variable. I did try changing the table column from
number to currency, and that made Access generate the zero.

Crystal is another matter. The data is coming from a database used
within a package, so I can't go changing the schema. I tried changing
the report field format to Currency, but that didn't help.

I tried to change Crystal's query statement to do a CONVERT, like SQL
Server allows. The documentation says it has a Convert function, but
when I execute the query, it says CONVERT is an unknown function.

Maybe it's time to hit the Crystal forum.

 

[Dirk Goldgar]

Hi all

I'm getting a weird anomaly when I use the summation function of
Access. For example, when I sum the following numbers:

274.28
68.57
34.71
1022.4
25.38
-1425.34

I should get 0 (zero). Instead, I get 2.27373675443232E-13.

If I change the 274.38 to 274.36, I should get -.02, but instead get
-0.019999999. which is almost -.02. Makes me think of the old Intel
processor errors.

The data originally came from a Firebird database, which was being
summed and printed on a Crystal report via ODBC. The value should
have been zero, and apparently is really, really close to zero, but
isn't quite. A zero prints, though I have zero-suppress on, and that
was my clue. So even Crystal is not summing to exactly zero.

That's when I imported (not linked) the data into Access 2003, and ran
the summation there, confirming that the numbers are not summing
correctly.

Rounding to two decimals does not fix the error.

So I question my math processor. I have a Compaq nx7000.

Thoughts and suggestions are welcome.

What type of field are these numbers stored in? Floating point formats
are inherently imprecise. If your field type is Number/Single, you
might try changing it to Number/Double to get better precision, but it
will still be imprecise. Or, if your numbers will never have more than
four decimal places, you might try changing the field type -- not its
format -- to Currency.

 

[renjith]

Hi all i am having the similar problem.I already designed table field with
tblnaname:receiptdet
fieldname: transferquantity

field size: double
Format :Fixed
Decimal Places:2

i have 90 roes and all ahving value of 51.20
i am getting sum as 4607.99999999999 instaead of 4608.
i want the result in 4608.00