[AccessD] Rounding with CCur conversion

Hi Lambert

You may have noticed the Round11 function of that page.

I modified it slightly to perform correct Banker's Rounding:

<code>
Private Declare Function CRoundBankers Lib "oleaut32.dll" _
  Alias "VarR8Round" ( _
  ByVal dblValue As Double, _
  ByVal lngDecimalPlaces As Long, _
  ByRef dblRounded As Double) As Long
'

Public Function RoundBankers( _
  ByVal dblNumber As Double, _
  Optional ByVal lngDecimalPlaces As Long) As Double
  
' Performs banker's rounding of number dblNumber.
'
' Idea by Lyle Fairfield, LyleFairfield at CyRiv.Com, 20001218
' Note: Performs no rounding if lngDecimalPlaces is negative.
'
' 2002-03-31. Gustav Brock, Cactus Data ApS, CPH.

  CRoundBankers dblNumber, lngDecimalPlaces, dblNumber
  
  RoundBankers = CDec(dblNumber)

End Function
</code>

Limitation is that is will not round below zero decimals but in most cases that is not an issue.
Advantage is, however, that it is very fast.

After the disappointment with CCur I may need to brush this up!

/gustav

>>> Lambert.Heenan at aig.com 02-05-2008 15:53 >>>
I'm glad I said that round "seems" to get it right. :-) 

But even though Round() in VBA/VB6 is buggy, it at least (seems) to attempt
banker's rounding.  I have not tried to test the 17 other rounds at the web
site, but I wonder if any of them correctly implement bankers rounding?

There's an interesting article of the subject here...
http://en.wikipedia.org/wiki/Rounding 

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 9:45 AM
To: accessd at databaseadvisors.com 
Subject: Re: [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