473,389 Members | 1,066 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,389 software developers and data experts.

Errors in Currency Rounding

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
Nov 12 '05 #1
3 9353
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

Nov 12 '05 #2
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.


Nov 12 '05 #3
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

Nov 12 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Lynn N. | last post by:
I have a report showing Rate, Hours and Total Pay (which is Rate*Hours) for several workers. I want to sum the Total Pay and get a CORRECT figure. This seems like it should be such a simple task....
11
by: tshad | last post by:
What is the best way to handle money in C#? Do you use float type? Then how do you handle rounding problems with cents as well as how best to display it? Thanks, Tom
3
by: Josh | last post by:
I am writing a program where the user inputs currency in US dollars. I want the program to only accept valid currency input, converting the string into the proper type of variable (double?), and...
6
by: Mitchell Vincent | last post by:
Just making sure I'm not missing the boat here, but are there any special routines for doing currency math (fixed precision stuff) in .NET? The wonderful problems of doing math on decimals tend...
9
by: Joe Attardi | last post by:
Hi all, Math is not my strongest area so forgive me if I use some of the wrong terminology. It seems that scientific notation is immune to rounding errors. For example: (4.98 * 100) + 5.51 ...
2
by: Senna | last post by:
Hi Have a method that rounds a currency value to the closest quarter. It works fine as long as the value is above zero, below zero it rounds to the upper integer. Any working solution is much...
1
by: Eric | last post by:
I've built a Currency class, more or less like Java's, a Money class to handle rounding and allocation, and a Moneybag that should collect different monies and convert them to a given exchange...
2
by: labcheung | last post by:
Hi all: I am working on Access 2000 with SP3. The problem is the sum of currency has $0.01 difference on the total even I use the same equation on all forms. I have a product which costs...
2
by: Smokey Grindel | last post by:
What is a good data type to store 4 digit precision currency in VB.NET? Double? Since it is only 4 digit precision wouldnt have any rounding problems right? thanks!
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
0
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
0
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
0
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...
0
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.