Results 1 to 12 of 12
  1. #1
    Join Date
    Oct 2003
    Posts
    19

    Unhappy 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. #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
    J. Paul Schmidt, Freelance Web and Database Developer
    www.Bullschmidt.com
    Access Database Sample, Web Database Sample, ASP Design Tips

  3. #3
    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. #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
    J. Paul Schmidt, Freelance Web and Database Developer
    www.Bullschmidt.com
    Access Database Sample, Web Database Sample, ASP Design Tips

  5. #5
    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. #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!
    J. Paul Schmidt, Freelance Web and Database Developer
    www.Bullschmidt.com
    Access Database Sample, Web Database Sample, ASP Design Tips

  7. #7
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1
    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. #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).
    J. Paul Schmidt, Freelance Web and Database Developer
    www.Bullschmidt.com
    Access Database Sample, Web Database Sample, ASP Design Tips

  9. #9
    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
    ' Copyright 2000; Sybex, Inc. All rights reserved.
    ' ---------------------------------------------------
    '
    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. #10
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Quote Originally Posted by jneuwirth View Post
    I got the definition for the Round function off 2. Built-in Functions — 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.
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  11. #11
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    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
    I'd rather be riding on the Tiger 800 or the Norton

  12. #12
    Join Date
    May 2016
    Posts
    1

    Smile 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
  •