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