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 + 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

 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
8 Replies

 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 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   Aug 23 '07 #2

 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 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)> Aug 23 '07 #3

 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 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   Aug 23 '07 #4

 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

 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

 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

 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 Public Function Round2(ByVal dblInput As Variant, ByVal intDigits As Integer) As Double     Round2 = Round(Str(dblInput * 10 ^ intDigits), 0) / 10 ^ intDigits End Function   Aug 24 '07 #8

 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. 