Errors in Currency Rounding

[Dalan]

Is there any code available to address currency rounding problems in
Access 97? Apparently, selecting currency type table fields does not
resolve the problem. For instance, in my form I have a price of item
field (say $49.95), and a percentage discount field (say 10% = $5.00),
and calculated net cost field of the two. Access seemingly doesn't
understand banker's rules as the resulting total is $44.96. Could this
be a bug? Why does it miscalculate with currency numbers?

Thanks for your comments. Dalan

[Allen Browne]

What is your understanding of banker's rounding - that 5 is rounded towards
the EVEN number? $44.96 would seem to be the closest even number ot $44.955.

There are a (very) few cases where floating point numbers do not round as
you would expect. You can deal with those by explicitly typecasting to
Currency, i.e.:
Round(CCur([Price] * (1 - [Discount])), 2)

Since the Currency type cannot be Null, you may need Nz() inside CCur() as
well.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html

"Dalan" <ot***@safe-mail.net> wrote in message
news:50*************************@posting.google.co m...

Is there any code available to address currency rounding problems in
Access 97? Apparently, selecting currency type table fields does not
resolve the problem. For instance, in my form I have a price of item
field (say $49.95), and a percentage discount field (say 10% = $5.00),
and calculated net cost field of the two. Access seemingly doesn't
understand banker's rules as the resulting total is $44.96. Could this
be a bug? Why does it miscalculate with currency numbers?

Thanks for your comments. Dalan

[Steve Jorgensen]

OK, just call me hasty the last week or so. I was incorrect in saying it
is necessary to convert (1) to currency before subtracting a currency from
it. VBA or the expression service automatically converts to Currency in
this case. Converting the constant to currency is only necessary if the
constant is a fraction.

On Tue, 16 Sep 2003 15:54:31 GMT, Steve Jorgensen <no****@nospam.nospam>
wrote:

On Tue, 16 Sep 2003 12:33:26 GMT, "Allen Browne"
<ab***************@bigpond.net.au> wrote:

What is your understanding of banker's rounding - that 5 is rounded towards
the EVEN number? $44.96 would seem to be the closest even number ot $44.955.

There are a (very) few cases where floating point numbers do not round as
you would expect. You can deal with those by explicitly typecasting to
Currency, i.e.:
Round(CCur([Price] * (1 - [Discount])), 2)

Since the Currency type cannot be Null, you may need Nz() inside CCur() as
well.

I've found it takes more than this. Every part of the expression must be
converted to currency so, for instance, assuming [Discount] is already
currency, you need Round(CCur([Price] * (CCur(1) - [Discount])), 2). Also,
if you ever divid, you have to wrap the division in a CCur(...) or that
part of the result will be converted to Double. Note that, if you read all
Microsoft's documentation, it would seem that CCur(<currency>/<currency>)
would always produce a Double, then conver to Currency, but testing has
shown that it works more intelligently, and actually does a Currency
division in this case, never producing an intermediate Double (certain
known cases of rounding error with Double division do not occur).

Next, most companies I've worked with compute a discount amount, then
subtract that from the price rather than computing Price*(1-Discount).
Doing it this way, the discount on 49.95 is 4.995 which rounds to 5.00.
Subtracting that from 49.95 gives 44.95. When the last digit of the price
is odd, these 2 methods of calculating the discount always produce
different results. It's a question of whether you're rounding the
discounted price, or rounding the discount.

[Hugh N. Ross]

I have also had trouble with rounding in Access Currency. The internal form
of currency is scaled integer but the implementation uses 4 digits of
fraction instead of just 2 (pennies). The result is still an indefinite
rounded result.
The only perfect solution I have found was to do the scaling myself. Use
long integer format and on entry multiply each value by 100. Do this for ALL
money variables. After any division, do a suitable rounding operation,
truncate and store in the long integer form again. This is the only way I
was able to get the calculated sum of a column of numbers to agree with the
actual sum.
Hugh

"Dalan" <ot***@safe-mail.net> wrote in message
news:50*************************@posting.google.co m...

Is there any code available to address currency rounding problems in
Access 97? Apparently, selecting currency type table fields does not
resolve the problem. For instance, in my form I have a price of item
field (say $49.95), and a percentage discount field (say 10% = $5.00),
and calculated net cost field of the two. Access seemingly doesn't
understand banker's rules as the resulting total is $44.96. Could this
be a bug? Why does it miscalculate with currency numbers?

Thanks for your comments. Dalan