[AccessD] Rounding with CCur conversion

Hi Lambert

> So plain old Round() seems to do it "right", but not Ccur()???

Well, Round is not old - it was introduced with A2000 - and it only does it right sometimes.
See (below) the test in the link I provided where it fails.

/gustav


>>> Lambert.Heenan at aig.com 02-05-2008 15:23 >>>
Access 2002:

? round(111.11115,4),round(1111.11115,4), round(11111.11115,4)
 111.1112      1111.1112     11111.1112 
? round(111.11105,4),round(1111.11105,4), round(11111.11105,4)
 111.111       1111.111      11111.111 

Round up if there is and odd digit left of the last decimal, and round down
for even digits. Bankers rounding.


? ccur(111.11115),ccur(1111.11115), ccur(11111.11115)
 111.1111      1111.1111     11111.1112 
? ccur(111.11105),ccur(1111.11105), ccur(11111.11105)
 111.1111      1111.111      11111.111 

Sometimes round up, sometimes round down. Chaotic rounding.

So plain old Round() seems to do it "right", but not Ccur()???

Lambert

-----Original Message-----
From: accessd-bounces at databaseadvisors.com 
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Gustav Brock
Sent: Friday, May 02, 2008 5:07 AM
To: accessd at databaseadvisors.com 
Subject: Re: [AccessD] Rounding with CCur conversion

Hi Asger

You are right, this is weird. Here are my results:

Access 2.0: 
  111.1111
Access 95: 
  Not installed
Access 97:
  111.1112
Access 2000:
  111.1111
Access 2002/XP:
  111.1111
Access 2003:
  111.1111
Access 2007:
  111.1111

Typically 1111.11115 and 11111.11115 are rounded as 1111.1111 and 11111.1112
respectively.
I have no idea what causes the difference in behaviour. Only Access 97 does
it consistently right.

In my opinion it is a bug from Access 2.0 corrected in Access 97,
reintroduced in Access 2000 and never removed.

/gustav


>>> ab-mi at post3.tele.dk 02-05-2008 10:30 >>>
Thanks for the information, Gustav. Now I know that CCur should use Banker's
rounding.
I still get 111.1111 for CCur(111.11115) though. Tried it in Office 2003 and
then in Office 2007 on a different machine (Virtual PC) - same result.
Strange.

Asger

-----Oprindelig meddelelse-----
Fra: accessd-bounces at databaseadvisors.com 
[mailto:accessd-bounces at databaseadvisors.com] På vegne af Gustav Brock
Sendt: 2. maj 2008 08:57
Til: accessd at databaseadvisors.com 
Emne: Re: [AccessD] Rounding with CCur conversion

Hi Asger

First, this:

  CCur(111.11115)

returns here 111.1112 as expected. Don't know why you get a different
result.

Then, the CCur, CInt, CLng and CByte functions perform Banker's rounding
while Int and Fix don't.

Further, Round is known to be buggy, so avoid that for any serious purpose.

If you are interested in rounding and how to optimise this regarding speed
while still being accurate, read on here:

  http://www.xbeat.net/vbspeed/c_Round.htm 

Note that the absolutely simplest (but slowest) method to perform 100%
correct (mathematical) rounding is ... Format!

/gustav

>>> ab-mi at post3.tele.dk 02-05-2008 01:55 >>>
Hi group,

 

I just noticed that the algorithm in VB for rounding seems to be different
for the Round-function and the CCur-function.

The Round-function use "Banker's rounding", also known as "Dutch rounding".
The CCur-function behaves differently:

Round(1.11115,4) --> 1.1112

Round(11.11115,4) --> 11.1112

Round(111.11115,4) --> 111.1112

CCur(1.11115) --> 1.1112

CCur(11.11115) --> 11.1112

CCur(111.11115) --> 111.1111  ?!!

 

What's going on in the last conversion? Is this a bug or does the
CCur-function have a special algorithm?

 

TIA

Asger