By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
428,587 Members | 623 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 428,587 IT Pros & Developers. It's quick & easy.

Round function - how it REALLY works

dima69
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.
Aug 23 '07 #1
Share this Question
Share on Google+
8 Replies


FishVal
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.
Expand|Select|Wrap|Line Numbers
  1. Public Function Round1(ByVal dblInput As Double, ByVal intDigits As Integer) As Double
  2.  
  3.     Dim intParity As Integer
  4.  
  5.     intParity = Int(dblInput) - Int(Int(dblInput) / 2) * 2
  6.     Round1 = Int(dblInput * 10 ^ intDigits + intParity / 2) / 10 ^ intDigits
  7.  
  8. End Function
  9.  
Aug 23 '07 #2

missinglinq
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!

Expand|Select|Wrap|Line Numbers
  1. Public Function RoundTotal(ByVal dblNumber As Double, ByVal intDecimals As Integer) As Double
  2.  
  3. '           : 0.5 is rounded up
  4. 'Parameters : dblNumber - number to round
  5. '           : intDecimals - number of demal places
  6. '             to round to
  7.  
  8. '           : (positive for right of decimal, negative for left
  9. 'Returns    : Rounded number
  10.  
  11.     Dim dblFactor As Double
  12.     Dim dblTemp As Double         ' Temp var to prevent rounding problems in INT()
  13.  
  14.     dblFactor = 10 ^ intDecimals
  15.     dblTemp = dblNumber * dblFactor + 0.5
  16.     RoundTotal = Int("" & dblTemp) / dblFactor
  17.  
  18. 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)>
Aug 23 '07 #3

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

Expand|Select|Wrap|Line Numbers
  1. Public Function Round1(ByVal dblInput As Variant, ByVal intDigits As Integer) As Double
  2.  
  3.     Dim intParity As Integer
  4.  
  5.     intParity = Int(dblInput) - Int(Int(dblInput) / 2) * 2
  6.     Round1 = Int(Str(dblInput * 10 ^ intDigits + intParity / 2)) / 10 ^ intDigits
  7.  
  8. End Function
  9.  
Aug 23 '07 #4

dima69
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 :)
Aug 24 '07 #5

FishVal
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. :)
Aug 24 '07 #6

dima69
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 !
Aug 24 '07 #7

FishVal
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.
Expand|Select|Wrap|Line Numbers
  1. Public Function Round2(ByVal dblInput As Variant, ByVal intDigits As Integer) As Double
  2.     Round2 = Round(Str(dblInput * 10 ^ intDigits), 0) / 10 ^ intDigits
  3. End Function
  4.  
Aug 24 '07 #8

dima69
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 !
Aug 24 '07 #9

Post your reply

Sign in to post your reply or Sign up for a free account.