Precision as Displayed (using SUM feature in an Access report)

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

When using the SUM formula against a column of numbers formated as currency,
the result of the sum is not correct. It is doing some sort of rounding
which results in the results being skewed by the extra decimal places in the
numbers being summed. In MSExcel there is a feature under
tools/options/calculation to select "precision as displayed" which corrects
this same problem in MSExcel. My question is, how is this "precision as
displayed" characteristic applied to my MSAccess report so the sum formula
produces the desired result?
 
TomM said:
When using the SUM formula against a column of numbers formated as currency,
the result of the sum is not correct. It is doing some sort of rounding
which results in the results being skewed by the extra decimal places in the
numbers being summed. In MSExcel there is a feature under
tools/options/calculation to select "precision as displayed" which corrects
this same problem in MSExcel. My question is, how is this "precision as
displayed" characteristic applied to my MSAccess report so the sum formula
produces the desired result?
Click to expand...


No such thing in Access. Use Sum(Round(field, places)
instead.
 
Thanks for the suggestion Marshall. I tried it.. using the syntax
Sum(Round(field, places), which translated I assume to Sum(Round([FieldName],
2) (Fieldname was the field being calculated, and 2 equals the number of
deciimal places I wanted. I assume this was right. The report ran, but
the calculation result was the same.

Thanks for he help though!
 
How are you determining that the sum is wrong? Probably by
using pen and paper to add up the values on each line of the
report. If so, those values are being displayed with two
places, which means they are also rounded to two places,
right? If so, they should be the same.

The math behind my thinking is:
Sum(Round(field)) = youraddition of rounded values
but, what you had originally (where the sum was implicitly
rounded for the display):
Round(Sum(field)) <> youraddition of rounded values

If you still think the sum is wrong, I'll need to see a
small example of the original values, the sum, how you're
calculating them and why you say they two results are
different.
--
Marsh
MVP [MS Access]


Thanks for the suggestion Marshall. I tried it.. using the syntax
Sum(Round(field, places), which translated I assume to Sum(Round([FieldName],
2) (Fieldname was the field being calculated, and 2 equals the number of
deciimal places I wanted. I assume this was right. The report ran, but
the calculation result was the same.

Marshall Barton said:
No such thing in Access. Use Sum(Round(field, places)
instead.
Click to expand...
Click to expand...
 
You are correct.. I hand calculated it.. aftere my accounting office had done
the same in detecting the discrepancy. I can send you some screen shots..
if you have an address for me to send them to. My e-mail address is
(e-mail address removed)

Thanks!

Marshall Barton said:
How are you determining that the sum is wrong? Probably by
using pen and paper to add up the values on each line of the
report. If so, those values are being displayed with two
places, which means they are also rounded to two places,
right? If so, they should be the same.

The math behind my thinking is:
Sum(Round(field)) = youraddition of rounded values
but, what you had originally (where the sum was implicitly
rounded for the display):
Round(Sum(field)) <> youraddition of rounded values

If you still think the sum is wrong, I'll need to see a
small example of the original values, the sum, how you're
calculating them and why you say they two results are
different.
--
Marsh
MVP [MS Access]


Thanks for the suggestion Marshall. I tried it.. using the syntax
Sum(Round(field, places), which translated I assume to Sum(Round([FieldName],
2) (Fieldname was the field being calculated, and 2 equals the number of
deciimal places I wanted. I assume this was right. The report ran, but
the calculation result was the same.

TomM wrote:
When using the SUM formula against a column of numbers formated as currency,
the result of the sum is not correct. It is doing some sort of rounding
which results in the results being skewed by the extra decimal places in the
numbers being summed. In MSExcel there is a feature under
tools/options/calculation to select "precision as displayed" which corrects
this same problem in MSExcel. My question is, how is this "precision as
displayed" characteristic applied to my MSAccess report so the sum formula
produces the desired result?
Click to expand...
Marshall Barton said:
No such thing in Access. Use Sum(Round(field, places)
instead.
Click to expand...
Click to expand...
Click to expand...
 
No, don't send it. I am willing to help you figure out the
problem, but I don't have the time away from paying clients
to do it for you.

You said before that the values are formatted as currency
(with two places?), does that mean the field is a Currency
type field? If it's a Single or Double type, that may
explain it.

Another thing you might try is to set the text box that
displays the values (Detail section?) to an expression:
=Round([FieldName], 2)
so that you're guaranteed that the value displayed is the
same as the value being Summed.
--
Marsh
MVP [MS Access]


You are correct.. I hand calculated it.. aftere my accounting office had done
the same in detecting the discrepancy. I can send you some screen shots..
if you have an address for me to send them to.

Marshall Barton said:
How are you determining that the sum is wrong? Probably by
using pen and paper to add up the values on each line of the
report. If so, those values are being displayed with two
places, which means they are also rounded to two places,
right? If so, they should be the same.

The math behind my thinking is:
Sum(Round(field)) = youraddition of rounded values
but, what you had originally (where the sum was implicitly
rounded for the display):
Round(Sum(field)) <> youraddition of rounded values

If you still think the sum is wrong, I'll need to see a
small example of the original values, the sum, how you're
calculating them and why you say they two results are
different.

Thanks for the suggestion Marshall. I tried it.. using the syntax
Sum(Round(field, places), which translated I assume to Sum(Round([FieldName],
2) (Fieldname was the field being calculated, and 2 equals the number of
deciimal places I wanted. I assume this was right. The report ran, but
the calculation result was the same.


TomM wrote:
When using the SUM formula against a column of numbers formated as currency,
the result of the sum is not correct. It is doing some sort of rounding
which results in the results being skewed by the extra decimal places in the
numbers being summed. In MSExcel there is a feature under
tools/options/calculation to select "precision as displayed" which corrects
this same problem in MSExcel. My question is, how is this "precision as
displayed" characteristic applied to my MSAccess report so the sum formula
produces the desired result?


:
No such thing in Access. Use Sum(Round(field, places)
instead.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top