Plagued by rounding errors 1

[TrekBiker]

I keep getting rounding errors when calculating amounts involving unit prices and quantities. All unit prices in their tables are formatted as Currency. These amounts below are in currency apart from the end ones, which are the true amounts.

[pre]
Item Quantity Unit Price Discount at 5% Value Unformatted Value
Product 1 1 92.11 4.61 (5%) 87.50 87.50
Product 2 2 93.41 4.67 (5%) 177.48 177.48
Product 3 1 92.11 0.00 (0%) 92.11 92.11
Total 357.09 357.09
Credit item
Product 4 0.25 92.11 23.03 23.0275

Subtotal 334.06 334.0625
VAT at 20% 66.81 46.8125
Displayed Grand Total 400.88 400.875

Would expect 334.06 + 66.81 to be 400.87
[/pre]

I know rounding is well discussed but I don't seem able to get sensible results. Does a currency amount have 2 or 4 decimal places behind the scene, and how can I force the 'expected' answer of 400.87?

 

[kjv1611]

Is the calculation happening on the form, in a query, or in VBA?

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[dhookom]

Currency has 4 decimal places. I believe what you are experiencing is "bankers rounding". Since the '7' in 400.8[highlight #FCE94F]7[/highlight]5 is odd, the '5' is rounded up. The number 400.8[highlight #FCE94F]6[/highlight]5 would be rounded down to 400.86.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[TrekBiker]

First, kjv1611. The calculations are in a form, with the data being entered in a subform based on a query.

Duane, yes have come across Banker's Rounding but it leaves the problem that when the data is transferred to an invoice the amount payable may be .01 different from the sum of the constituents. This would look strange since the customer sees both the constituents and their sum.

I wonder if it's possible to 'freeze' the constituent amounts as numbers with just two decimal places so the result looks correct, something like Clng(Amount *100)/100?

 

[dhookom]

TrekBiker,
You might find some solutions at Allen Browne's web page.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[TrekBiker]

What looks to work is the Round function, so Round([Subtotal],2) + Round([VAT],2) gives the 'expected' answer 400.87 without a straight sum further rounding two numbers each with 4 decimal places.

I look at Allen Browne's articles frequently, thanks, and he does indeed talk a lot about rounding. I find it a bit alarming that having used rounding in calculations to get currency values for various items, Access then rounds the sum of these rather than just adding them.

I'm left wondering how far I have to use the Round fix as there are a large number of currency manipulations in my database.

 

[dhookom]

I think you could use
=Sum(Round([Numeric Field],2))

I'm not an accountant so I have no idea what is expected.


Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[TrekBiker]

Thanks for help Duane, the truth will out in time.

 

[Darrylles]

I refuse to use 'currency'.
Is it really a true 'data-type'?
Is it Microsofts attempt at 'cornering the market' / patenting a 'word'?
Could MS's 'currency' be naturally / accurately transferred to another database product?

I don't think so (and I've experienced enough Access 'currency' glitches to steer clear).
It's basically based on a 'business rule', that MS has defined.

Trouble is: no accountant will EVER state a rounding rule as 'global fact', it's whatever the current company has as a 'business rule'.

Stick to single / double data-types - they are fundamentally 'standard', and you can round up down or trim - as required.
Sometimes you have to stick to globally, historically accepted 'facts', and forget the 'clever' programming tricks.

D