Expert 100+
P: 181

May be somebody can explain to me how Round function works in Access. This article claims VBA6 uses banker's rounding, which means that exact half rounds to the closest even digit. This article claims VBA6 uses banker's rounding ONLY when rounding to integer.
So how do I get Round(2.245, 2) = 2.24 while Round(1.245, 2) = 1.25 ?
I use both Acc2K and Acc2K2.
 
Share this Question
Expert 2.5K+
P: 2,653

May be somebody can explain to me how Round function works in Access. This article claims VBA6 uses banker's rounding, which means that exact half rounds to the closest even digit. This article claims VBA6 uses banker's rounding ONLY when rounding to integer.
So how do I get Round(2.245, 2) = 2.24 while Round(1.245, 2) = 1.25 ?
I use both Acc2K and Acc2K2.
Hi, dima.
As far as I've understood you, you mean rounding to lowest/uppest digit depending on parity of integer part.
If so, the function below does it. 
Public Function Round1(ByVal dblInput As Double, ByVal intDigits As Integer) As Double


Dim intParity As Integer


intParity = Int(dblInput)  Int(Int(dblInput) / 2) * 2

Round1 = Int(dblInput * 10 ^ intDigits + intParity / 2) / 10 ^ intDigits


End Function

  Expert 2.5K+
P: 3,532

Actually, I didn't read the second article as saying that VBA uses banker's rounding ONLY when rounding to integer. What I read was that if the integer portion of the number was even Access would round down, which is to say the same thing the first article said, that Access always rounds to the closest even integer.
Here's a function that I think will do what you want. Place it in a standard module. If you don't have a standard module to place it in, create one for it. Just remember do not name the module the same name as the function! This confuses Access!  Public Function RoundTotal(ByVal dblNumber As Double, ByVal intDecimals As Integer) As Double


' : 0.5 is rounded up

'Parameters : dblNumber  number to round

' : intDecimals  number of demal places

' to round to


' : (positive for right of decimal, negative for left

'Returns : Rounded number


Dim dblFactor As Double

Dim dblTemp As Double ' Temp var to prevent rounding problems in INT()


dblFactor = 10 ^ intDecimals

dblTemp = dblNumber * dblFactor + 0.5

RoundTotal = Int("" & dblTemp) / dblFactor


End Function
The, in code, call it just like Round() except use
RoundTotal( YourNumberToRound, NumberOfDigits)
Linq ;0)>
Edit: Sorry, Fish, got distracted midpost! ;0)>   Expert 2.5K+
P: 2,653

Nice hint, Link.
I mean this  converting number to string before passing to Int function.
I was near trying to solve the problem with Format function, but your solution is really nice. 
Public Function Round1(ByVal dblInput As Variant, ByVal intDigits As Integer) As Double


Dim intParity As Integer


intParity = Int(dblInput)  Int(Int(dblInput) / 2) * 2

Round1 = Int(Str(dblInput * 10 ^ intDigits + intParity / 2)) / 10 ^ intDigits


End Function

  Expert 100+
P: 181

Linq, FishVal  thanks for the tips, but I think you've got me wrong. What I am looking for is to understand how an existing Round function works, cause it seems that it works differently than Banker's round (see examples in my first post).
I was unaware of that weird behavior before, so now if I change the standard Round to something else, this may cause existing invoices to change :)
  Expert 2.5K+
P: 2,653

Linq, FishVal  thanks for the tips, but I think you've got me wrong. What I am looking for is to understand how an existing Round function works, cause it seems that it works differently than Banker's round (see examples in my first post).
I was unaware of that weird behavior before, so now if I change the standard Round to something else, this may cause existing invoices to change :)
It does Banker's rounding  rounds to the nearest even digit
Round(2.245, 2) = 2.24
Round(2.255, 2) = 2.26
From your example
So how do I get Round(2.245, 2) = 2.24 while Round(1.245, 2) = 1.25 ?
I understood that you expect it to round "half" to lower digit if integer part is even, and to upper digit if it is odd. Am I right?
To the best of my knowledge Round function doesn't work so, as well as any other standard rounding function. :)
  Expert 100+
P: 181

It does Banker's rounding  rounds to the nearest even digit
Round(2.245, 2) = 2.24
Round(2.255, 2) = 2.26
From your example
I understood that you expect it to round "half" to lower digit if integer part is even, and to upper digit if it is odd. Am I right?
NO. Nothing to do with Integer part parity.
Following the nearest even digit algorithm, I expect Round(1.245, 2) = 1.24 (4 is the nearest even digit)  am I right ?
But what I really get is 1.25 !
  Expert 2.5K+
P: 2,653

NO. Nothing to do with Integer part parity.
Following the nearest even digit algorithm, I expect Round(1.245, 2) = 1.24 (4 is the nearest even digit)  am I right ?
But what I really get is 1.25 !
Weird.
Really when NumDigitsAfterDecimal>0, rounding direction is somewhat unpredictable. I suppose that this caused by VBA math precision.
As for me I would prefer to use smthng like this to get result of Banker's rounding. 
Public Function Round2(ByVal dblInput As Variant, ByVal intDigits As Integer) As Double

Round2 = Round(Str(dblInput * 10 ^ intDigits), 0) / 10 ^ intDigits

End Function

  Expert 100+
P: 181

Amaizing !
When data type is Currency or Decimal, Round works as it supposed to (Banker's round).
But when the data type is double, Round result is something undefined !
For example:
Round(CCur(1.245) ,2) = 1.24 (as expected)
Round(CDbl(1.245) ,2) = Round(1.245 ,2) = 1.25 ! Wrong !
    Question stats  viewed: 3810
 replies: 8
 date asked: Aug 23 '07
