# Thread: "Round" Function not rounding correctly!!

1. Registered User
Join Date
Oct 2003
Posts
19

## Unanswered: "Round" Function not rounding correctly!!

I got the definition for the Round function off http://www.python.org/doc/current/li...-in-funcs.html
which states:

"round( x[, n])

Return the floating point value x rounded to n digits after the decimal point. If n is omitted, it defaults to zero. The result is a floating point number. Values are rounded to the closest multiple of 10 to the power minus n; if two multiples are equally close, rounding is done away from 0 (so. for example, round(0.5) is 1.0 and round(-0.5) is -1.0). "

However, for example, the number being rounded in my case is 404.685 rounded to 2 decimals. Shouldn't the number round to 404.69?? It doesn't - it rounds to 404.68!! It treats all other decimals the same way. If I tell it to round 404.6851 THEN it rounds to 404.69. Am I crazy? Isn't ...5 suposed to round up?

FYI: I'm currently using this "Round" formula in a number of places in this database and I think it's worked previously, but now it doesn't

Any help would be much appreciated!!
(Using Access 2000 SR-1)

2. Join Date
Jun 2003
Location
USA
Posts
1,032
Round() function apparently has a serious flaw which of course Microsoft apparently considers a feature (called banker's rounding which has a bias toward evens) in that a 5 digit rounds up only if the digit to the left is an odd digit, if even it rounds down.

Examples:
0.5 -> 0
1.5 -> 2
2.5 -> 2
3.5 -> 4
4.5 -> 4
5.5 -> 6
6.5 -> 6
7.5 -> 8
8.5 -> 8
9.5 -> 10

3. Registered User
Join Date
Oct 2003
Posts
19

## You've got to be kidding me...

No Waaaaaaay!!!!!!!!!!!

Aaaaargh!!! That's the worst news I've ever heard!

(However I appreciate immensely that you responded and gave me that info) - Is there anything I can do about this? It's screwing up our whole invoicing system.

In case you're interested:
We process billing for 50 temp staff across 5 different temp agencies and anytime someone goes into overtime ([Bill rate]*1.5) * [OT Hours] it runs the risk of making our total amount cents off.

Any ideas? There's no global setting where I can check (Don't use Banker's Rounding)? - of course not - that would be much too user-friendly.

Hating Microsoft right now.

4. Join Date
Jun 2003
Location
USA
Posts
1,032
I've got my own little rounding function called jpsRound() which you can put in any module and use just like Round().

Public Function jpsRound(ByVal pvarNumber As Variant, ByVal pintDecimals As Integer) As Variant
' Purpose: Rounds, returning a variant.

On Error GoTo Err_jpsRound

' Dim var.
Dim varNumber As Variant
Dim varBigValue As Variant

' Set var.
' If incoming value is null (possibly from a blank field),
' convert it to 0.
If IsNull(pvarNumber) Then
varNumber = 0
Else
varNumber = pvarNumber
End If

' Convert 3.456 to 2 decimal places up to be 345.6
varBigValue = varNumber * 10 ^ pintDecimals

' Add .5 to the above 345.6 to get 346.1.
' Then chop off the fractional .1 to leave 346.
' Then convert 346 back down to 3.46.
' (But if the orig number is neg., then subtract .5 instead.)
If varNumber >= 0 Then
jpsRound = Fix(varBigValue + 0.5) / 10 ^ pintDecimals
Else
jpsRound = Fix(varBigValue - 0.5) / 10 ^ pintDecimals
End If

Exit Function

Err_jpsRound:
MsgBox "Error " & Err & "." & Chr(13) & Chr(10) & Chr(10) & Err.Description & ".", vbExclamation
Exit Function

End Function

5. Registered User
Join Date
Oct 2003
Posts
19

## Tears in my eyes!!!!

Sir you have made my day -

First let me say I don't really know Visual Basic from a hole in the ground - but I followed your advise and it works!!

All I can say is Thank you thank you thank you!!!!

I'm greatful there are people like you that help those of us that are less knowledgeable!

Have a great day Bullschmidt! - By the way - I visited your website and it's really cool.

6. Join Date
Jun 2003
Location
USA
Posts
1,032
And thank you for all the kind words - you've definitely made my day too!

7. Registered User
Join Date
Oct 2003
Location
Ger
Posts
1,969
Here is a compact version to round a number

PHP Code:
Function Round(anyNumdigits) As Variant
Round
= (Int((anyNum 0.5 10 digits) * 10 digits) / 10 digits)
End Function

8. Join Date
Jun 2003
Location
USA
Posts
1,032
And here are a few extra comments from my function (originally written for an earlier version of Access) sort of explaining why it wasn't so compact:

' Remarks: Done in 2 steps so that floating point values will
' still be handled correctly as recommended in
' Microsoft Knowledge Base Article Q1111781.
' Otherwise jpsRound(1.555,2) would return 1.55
' instead of 1.56 if underlying data type were
' floating such as double instead of currency.
'
' If wrapped varNumber * 10 ^ pintDecimals
' in CDec as follows varBigValue = CDec(varNumber * 10 ^ pintDecimals)
' then doesn't work in a control or in a query field (but does work in code),
' but would like to use CDec() so 9.575 returns 9.58 instead of 9.57.
'
' Fix() truncates positive and negative numbers.
' Examples: 8.3 -> 8, 8.7 -> 8, -8.3 -> -8, -8.7 -> -8
' Int() would truncate positive numbers but would make
' negatives go more negative (-8.3 -> -9).

9. Registered User
Join Date
Dec 2009
Posts
1

## Issue with the current solution

These solutions work to properly round a number (well, what I consider to be proper) but when trying to sum the output of multiple rounded fields within the same query I had numbers concatenating instead of summing. I looked around a bit and found this bit of code by Ken that solved my problem. Hope it helps future poor saps like myself save an hour or two.

Best,
Shawn

The code as seen at the above link
' ********** Code Start **************
'This code was originally written by Dev Ashish
'It is not to be altered or distributed,
'except as part of an application.
'You are free to use it in any application,
'provided the copyright notice is left unchanged.
'
'Code Courtesy of
'Dev Ashish
'
Public Function Round( _
ByVal Number As Variant, NumDigits As Long, _
Optional UseBankersRounding As Boolean = False) As Double
'
' ---------------------------------------------------
' From "Visual Basic Language Developer's Handbook"
' by Ken Getz and Mike Gilbert
' ---------------------------------------------------
'
Dim dblPower As Double
Dim varTemp As Variant
Dim intSgn As Integer

If Not IsNumeric(Number) Then
' Raise an error indicating that
' you've supplied an invalid parameter.
Err.Raise 5
End If
dblPower = 10 ^ NumDigits
' Is this a negative number, or not?
' intSgn will contain -1, 0, or 1.
intSgn = Sgn(Number)
Number = Abs(Number)

' Do the major calculation.
varTemp = CDec(Number) * dblPower + 0.5

' Now round to nearest even, if necessary.
If UseBankersRounding Then
If Int(varTemp) = varTemp Then
' You could also use:
' varTemp = varTemp + (varTemp Mod 2 = 1)
' instead of the next If ...Then statement,
' but I hate counting on TRue == -1 in code.
If varTemp Mod 2 = 1 Then
varTemp = varTemp - 1
End If
End If
End If
' Finish the calculation.
Round = intSgn * Int(varTemp) / dblPower
End Function
' ********** Code End **************

10. Moderator
Join Date
Jun 2005
Location
Richmond, Virginia USA
Posts
2,764
Originally Posted by jneuwirth
I got the definition for the Round function off 2. Built-in Functions &mdash; Python v2.6.4 documentation
Just out of curiosity, why would you think that the definition of the Round function from one programming language, Python, would apply to another totally unrelated language, Access VBA?

VBA is not even consist within itself, which is to say that there are differences between straight Visual Basic, Access VBA and Excel VBA. There are many cases of functions in these languages having identical names but whose functions are different.

Join Date
Nov 2004
Location
out on a limb
Posts
13,692
Bankers rounding is just as valid as other forms of rounding

theres plenty of options, and if you don't like the options then write a function of your own that does the rounding the way you want.

just because Python define their function called round doen't meant that any/every other language will do the same. As Missinglinq points out the defintion you picked up was Pythons defintion which is valid within Python and perhaps other languages. if you are usign a function in Access that you haven't used before then refer tot he documentation for that function within Access, either is a book a web page or within the help system. in this instance the round function is working correctly, it is your expectations which are incorrect

it wouldn't at all surprise me if the orginal version of Access was atrgetted more at financial institutions where bankers rounding is the 'obvious' choice

there is more consistency in dialects of the same language such as SQL. look at the havoc caused with dates, the JET engine only "likes" dates in US format as opposed to the localistaion settings of the computer doing the actual work..

still its a fantastic bit of post tombstoning to wake up a 5 year old thread. it also shows why users shoudl always use the search and should alwasy post solutions to problems so others can learn from others experience

12. Registered User
Join Date
May 2016
Posts
1

## Alternative function

You can use that function:

Function RoundOK(Num As Double, Precisao As Byte) As Double

RoundOK = Round(Num + 5 / (10 ^ (Precisao + 2)), Precisao)

End Function

It rounds the number and returns the correct answer.

Best regards,

Mário Lemos Cabral

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•