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 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***@safemail.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
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*(1Discount). 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.
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***@safemail.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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
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....

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

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...

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...

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 ...

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...

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...

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...

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!

by: Charles Arthur 
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone

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

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...

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 columnwise with in the specific length.
suppose the i have to...

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...

by: marktang 
last post by:
ONU (Optical Network Unit) is one of the key components for providing highspeed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...

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...

by: Oralloy 
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bitfields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...

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...
 