Off by a penny

[jcright]

Hello,

I have a report with a calculated field in the report footer that is off by a penny. I need help figuring out why this is happening and what I can do to fix it.

My report uses three fields from my table: "Sale", "Instrument", "Store". The "Sale" field is set to currency with 2 decimal places.

The report has a query in the record source so that I can filter out certain stores and sum up my "Sale" field. In the query "Sale" is set to currency with two decimal places. The resulting field is "SumOfSale" which I use in the detail section.

It also uses 3 fields that are calculated: "Factor" and "subTotal" in Detail section and "Total" in Report footer. "subTotal" and "Total" are set to currency with two decimal places. Both "subTotal" and "Total" have the same exact calculations except the "Total" field uses the Sum function. Both also use the Iif function. All of these are in text boxes.

"Factor" is set to percent with two decimal places but it is really only there so that the person reading the report can verify that we are using the correct factor.

All three fields use an Iif function because the "Factor" is based on the store.

Here is the equation for the "subTotal": =IIf([Store]="Warehouse",[SumOfSale]*0.02,IIf([Store]="Bloomfield",[SumOfSale]*0.005,IIf([Store]="Canton",[SumOfSale]*0.01,0)))

Here is the equation for the "Total": =Sum(IIf([Store]="Warehouse",[SumOfSale]*0.02,IIf([Store]="Bloomfield",[SumOfSale]*0.005,IIf([Store]="Canton",[SumOfSale]*0.01,0))))



When I go to view, everything in the "subTotal" column is calculated correctly. My "Total" though shows 1 penny off. I don't really understand how a penny gets lost when I'm using the same formula. I wanted to just sum up the "subTotal" field but apparently you can't do that on a calculated field, only on a record source?

I'm also open to suggestions on how to build this better.

Thanks

[Bob Fitz]

Can you post a copy of the db with a couple of ficticious records to illustrate the problem.

[CJ_London]

When I go to view, everything in the "subTotal" column is calculated correctly.

Think what you mean to say is 'When I go to view, everything in the "subTotal" column appears to be calculated correctly.' My guess is you are using the format property to display 2 dp's, but you are multiplying by 0.005, 0.01 etc which will have additional dp's

say sumofsale is 200.20

x .02=4.004 - but you are seeing 4.00 because of your format - all those 0.004's will add up towards your 1p difference

you need to include the round function in your calculation

"subTotal": =Round(IIf([Store]="Warehouse",[SumOfSale]*0.02,IIf([Store]="Bloomfield",[SumOfSale]*0.005,IIf([Store]="Canton",[SumOfSale]*0.01,0))),2)

[jcright]

Hi Ajax,
You are correct, "...it appears..." is more accurate.

I forgot to mention that I did think of using the Round function. I used it on the "Total" field though, not the "subTotal" field. So yes, I found my penny. Thank you. However, I don't think it rounded correctly. It has been my understanding that 1-4 rounds down, 5-9 rounds up. I have one sale total of $18185 at a factor of .005 which gives me $90.925. I would have expected it to round up to $90.93?

[CJ_London]

Think you need to google 'bankers rounding'. I haven't seen it as a topic for a while but it was quite 'popular' a few years ago. Here is a brief description

Bankers rounding

The Round() function in Access uses a bankers rounding. When the last significant digit is a 5, it rounds to the nearest even number. So, 0.125 rounds to 0.12 (2 is even), whereas 0.135 rounds to 0.14 (4 is even.)
The core idea here is fairness: 1,2,3, and 4 get rounded down. 6,7,8, and 9 get rounded up. 0 does not need rounding. So if the 5 were always rounded up, you would get biased results - 4 digits being rounded down, and 5 being rounded up. To avoid this, the odd one out (the 5) is rounded according to the previous digit, which evens things up.

[Bob Fitz]

Hi Ajax,
You are correct, "...it appears..." is more accurate.

I forgot to mention that I did think of using the Round function. I used it on the "Total" field though, not the "subTotal" field. So yes, I found my penny. Thank you. However, I don't think it rounded correctly. It has been my understanding that 1-4 rounds down, 5-9 rounds up. I have one sale total of $18185 at a factor of .005 which gives me $90.925. I would have expected it to round up to $90.93?

I think that may be because of something called Bankers Rounding. See the following link for an explaination and solution: http://allenbrowne.com/round.html

[orange]

More info than you may ever need regarding Round functions with vba.

[jcright]

Thanks for the responses on the banking and rounding. I had never heard of that before. Good to know.

This solves everything

[CJ_London]

Only other thing I would mention is that rounding should be done at the individual record level - in an invoice for example, VAT should be calculated on each item record and summed separately to the (net) invoice total

as an aside, you can simplify your calculation a bit

Sum([SumOfSale]*IIf([Store]="Warehouse",0.02,IIf([Store]="Bloomfield",0.005,IIf([Store]="Canton",0.01,0))))

I would also suggest that you should not really be hardcoding values - you should have a stores table with a multiplier field

tblStores
StorePK....StoreName....Multiplier
1.............Warehouse.....0.02
2..............Bloomfield.....0.005
3..............Canton..........0.01

then your calculation would simply be

Sum([SumOfSale]*[Multipier])