Sum(Round) gives incorrect value.

[chris-uk-lad]

Hi,

I have along list of values of which i need to sum together. The issue i have is, with the list (sorry in advance):



3812.743271
326.475432
236.291316
224.359102
221.09061
221.037702
220.97087
220.913642
220.863603
220.802808
220.737501
220.673351
220.473682
220.390728
220.319768
220.248771
220.166363
220.088685
219.997882
219.91699
219.835016
219.761373
217.873841
213.681428
186.373938
101.859653
56.800437
49.57262
43.024651
40.366354
34.482009
34.376974
34.191122
34.055436
34.023072
33.928298
33.792306
33.658003
33.657723
33.584295
33.506796
33.455555
-101.859653
-186.373938
-596.475651
-8999.713735

I use SUM(Round(value,6)) and get the result "1.19371179607697E-12". What i expect, and what has been worked out in Excell / calculator is zero. I believe that Access 97 has some mathematical issues with decimal sums? Is there a way to get the desired result?

The field type on the value is (Double, Fixed, 6dcp). I was working on this yesterday with 1dcp and was fine with smaller values.

Thanks

 

[vbaInet]

I guess this is related to your other thread.

Round(Sum([value]*1000000)/1000000,6)

The problem you're experiencing is because you are not using the right Field Size. If you set your Field Size to Double and the Decimal Places to 6, your current code should work.

By the way, DSum() sums in the format you're after.

 

[chris-uk-lad]

I guess this is related to your other thread.

Round(Sum([value]*1000000)/1000000,6)

The problem you're experiencing is because you are not using the right Field Size. If you set your Field Size to Double and the Decimal Places to 6, your current code should work.

By the way, DSum() sums in the format you're after.

"The field type on the value is (Double, Fixed, 6dcp). "

But anyway, your suggestion of 1000000/1000000 has worked thanks alot for the help over the last 2 days.

 

[vbaInet]

You're welcome!