Round function - how it REALLY works

[dima69]

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.

[FishVal]

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
 

[missinglinq]

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 mid-post! ;0)>

[FishVal]

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
 

[dima69]

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 :)

[FishVal]

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

[dima69]

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 !

[FishVal]

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
 

[dima69]

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 !