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.

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 wrote:
> 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.

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.

"DanG" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)
> 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.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

[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