Sum in report is out by a penny

[delph]

Hi, I wonder if anyone can help please. I have a report in Access 2000 with footer sums which has been working fine but is now out by a penny either up or down depending if it's a negative figure or positive. It looks like it might be trying to round up and down. Please can anyone shed any light on how to solve this? The calculated sum fields are all set with format = currency & decimal places =2. Many thanks in advance.

 

[spikepl]

How do you know that they are a penny out? What numbers did you calculate that on? Currency is with 4 decimal places, so when you reduce to 2 decimals you will experience rounding, unless all the numbers making up the sum had .xy00 as the decimal values

 

[delph]

Thanks for your reply spikepl, I have a summary report which totals different types of order & also one which details all the individual transactions. The transactions all appear correct, just some of the subtotals in the summary report are a penny out. Thanks

 

[Frothingslosh]

I ran into this once. Check to make sure that the numbers being used for the calculations aren't holding more digits than are showing on the report. As spike mentioned, the currency data type holds 4 decimal places, even though it only shows 2. That means that while you may, for example have three values that show 12.00, 11.00, and 10.00, they are actually containing the values 12.004, 11.004, and 10.004. Then when you add them, you wind up getting 10.00 + 11.00 + 12.00 = 33.01, because adding the TRUE values generated 33.012, which was then rounded to two digits.

 

[delph]

Hi Frothingsosh, thanks for your reply, I'll have a check. I think all the fields are set to currency so would I need to change this to general number or fixed with 2 decimal places? Apologies if this is really obvious! Thanks.

 

[Frothingslosh]

Use the Round function to make certain. I'd keep it as currency, however.

 

[spikepl]

The number of decimals you specified in formatting does not affect the values held, just their display, as Frothingslosh has illustrated! Pay attention to that difference.

If your display operates with 2 decimals everywhere, all your operations must do likewise, so that the numbers you displya (and sum) must be rounded off to 2 decimals (by using the Round fiunction, not just by setting number of decimals displayed to 2). Look up the documentation for that function, becasue it uses banker's rounding, and not what we 'd normally expect. Somewhere there is a normal Round function you can use instead, if not happy wioth banker's rounding.

I think all the fields are set to currency so would I need to change this to general number or fixed with 2 decimal places

NOOO! Currency is both data type and a specific data format - two different things. the two you mention are FORMATS, they would not chnage the result of your sum, but might change what is displayed.

AS an experiment, set the display as you have it to show 4 decimal places in all controls. If you do not have 00 everywhere as the last two digits then that is the problem! To deal with it is the Round function.

 

[delph]

To Frothingslosh & spikepl, thanks for your replies & link. I've done some further investigations & reading up on the Round function & all now works - thanks again for your help