Full Version: Sum
CurlieQ1034
Why is it that when I sum about 13 different numbers that have only 2 decimal places does the answer show up looking like this:
79.379605054855
I am using Sum([GrossAcre]) in the form footer of a subform. My numbers look like this:
53.020
98.280
How is this possible???
niesz
It is summing the underlying datatype (probably single or double)
se:
Expr1: Format(Sum([YourNumber]),"#.000")
With "Expression" in the Totals row
CurlieQ1034
If I were to use DECIMAL, would that help fix the problem??
niesz
What problem? I don't think using decimal will return what you want either.

EDIT: Let me clarify... If you always want a number with 3 decimal points, you will need to use a Format function somewhere....might as well do it in the query. If you need this value in a control you can use:

=Format(Sum([YourNumber]),"#.000")
CurlieQ1034
Problem being Sum doesn't work exactly like a calculator. When I add the values I get a number, yet when I Sum the values in Access I get a totally different number...

Ok, let me try that...

Edited by: CurlieQ1034 on Wed Dec 14 11:31:06 EST 2005.
niesz
Can you provide sample data with datatypes used in the table?
CurlieQ1034
Here's an attached of sample data.
niesz
When I sum these values manually I get 391.347
If I use:
=Format(Sum([Number]),"#.000")
in the control....I get 391.347
fkegley
I devised my own RoundOff function to handle problems like this. Access uses the internal value not the one you see on the screen, also some decimal numbers cannot be stored exactly so a very close approximation is used.
ormat does not change the actual number, only the way it is displayed. To change the actual number, you need to remove all those extra digits that you don't want. While Access has a built-in ROUND function, it does not do it the way you and I learned in school, which of course is the way I want it done.
Public Function RoundOff(N As Double, P As Integer) As Double
ON = N * 10 ^ P
N = N + .5
N = Int(N) / 10 ^ P
RoundOff = N
End Function
=Sum(RoundOff([GrossAcre], 3)) would round off the GrossAcre values to 3 decimal positions.
CurlieQ1034
Wow... great function Frank! Thanks!
fkegley
You're welcome, Jen, I hope it helps.
Jack Cowley
In addition to the Format() function you can set the controls Format to Fixed and Decimals to 3.
Just another way to give you the display that you want...
Jack
CurlieQ1034
Hey guys... how about 5 decimal places??? I am having trouble with a new set of #s. See attached.

EDIT: The Sum shows 818.89999, but on the calculator I get 818.90

Edited by: CurlieQ1034 on Wed Dec 14 14:11:45 EST 2005.
CurlieQ1034
Problem arises that on rounding to 5 decimal places, the 6th place is a "3", therefore doesn't round the .89999 to .90
fkegley
This sounds like a problem with the way Access stores numbers. Not every decimal number can be represented EXACTLY in binary. So it sometimes has to use an approximation when it stores the number. You have just run into that. I don't know what to do about this because it is built in to the way a computer stores numbers. You might try changing the type, may have already, to one that stores fewer digits, such as Single or Currency and see if that gives you the ability to get the answer to five decimal places. Other than that, I haven't anything to offer.
CurlieQ1034
Well, OK. I completely understand what you're saying. The only thing is... how does windows' calculator come out with the answer you and I "learned in school"??? No big deal I guess, just wondering how the "wizards" at Microsoft handled the problem.
CurlieQ1034
By the way, I did a test with this - summing one number. Numbers, with a .4 decimal place, before 128.4 come out great. Yet 128.4, rounded to the 5th place = 128.39999
hen, all the numbers after that and before 511.4 all end in ###.4. Yet, 511.4 = 511.40002. Same goes until 2048.4= 2048.39999.
Useless knowledge I guess... I just find it odd.
CurlieQ1034
Frank,
changed the type to currency, and I think that may have solved it. I still need to run some more tests, but I think that's it.
fkegley
Jen, that' s good. I believe now that I think about it, type Currency only holds 4 decimal places. At any rate, if it is working, that's the main thing.

From the help files,

"Currency values and numeric data used in mathematical calculations involving data with one to four decimal places. Accurate to 15 digits on the left side of the decimal separator and to 4 digits on the right side."

You're welcome.
Edited by: fkegley on Wed Dec 14 16:08:49 EST 2005.
CurlieQ1034
That's exactly what I found. I like it better than the Number type because it stores unnecessary numbers, yet I usually work with 5-8 decimal places. So, since what I showed you only happens about 1% of the time, I'll just keep using Number.
really appreciate all of your help today Frank!! Thanks so much!
fkegley
You're welcome, Jen. That sounds like a good idea to me. I like your new picture better than the previous one.