Results 1 to 12 of 12
  1. #1
    3 Star Lounger
    Join Date
    Apr 2003
    Location
    Calgary, Alberta
    Posts
    327
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Round Not Happening Correctly (Access 2000)

    I have a problem whre the rounding is not happening properly. In a query, I have Round("BC * KitAmount, 2) which would give 10.025. For some reason, it will round to 10.02 instead of 10.03. Any ideas how to fix this?

    Thanks

  2. #2
    3 Star Lounger
    Join Date
    May 2002
    Location
    Toronto, Ontario, Canada
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Round Not Happening Correctly (Access 2000)

    Okay... I'm completely confused... Since when does Access have a Round function?
    I know I'm still using Access 97 at work, but I have 2000 on my home machines and I've never seen that one...
    I'm going to have to peek when I get home... <img src=/S/laugh.gif border=0 alt=laugh width=15 height=15>

    Wish I could help... <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>

  3. #3
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Round Not Happening Correctly (Access 2000)

    This behavior is "by design". VB will round .5 to nearest even number. Example:

    ? Round(10.015,2)
    10.02
    ? Round(10.025,2)
    10.02
    ? Round(10.035,2)
    10.04
    ? Round(10.045,2)
    10.04

    This is explained in MSKB article 28855:

    CINT/CLNG Integer Assignment Round x.5 to Nearest Even Integer

    Excerpt:

    "When a numeric expression ending in .5 is assigned to an integer variable, a compiled Basic program will round the expression to the nearest even integer. For example, .5 converts to 0, 1.5 converts to 2, 2.5 converts to 2, and 3.5 converts to 4. This rounding to the nearest even integer occurs for the CINT and CLNG functions and for an integer division assigned to an integer or long integer variable. This behavior is a feature of the IEEE Floating Point Standard.

    "This type of integer rounding complies with the following IEEE standard:

    'If the difference between the unrounded operand and the rounded result is exactly one half, the rounded result is even' (Section 5.5 of the "IEEE Standard for Binary Floating-Point Arithmetic")

    "The purpose of this behavior is to prevent an average upward (or downward) bias as various calculations are rounded. If the number is always rounded up, there would be an upward bias in calculations. Rounding to the nearest even number averages out; therefore, no rounding bias occurs."

    See article for further details. Can't argue with the "IEEE Standard for Binary Floating-Point Arithmetic"....

    Re: Excel Round function: see MSKB article 194983:

    PRB: Round Function different in VBA 6 and Excel Spreadsheet

    "The Round() function in an Excel spreadsheet uses Arithmetic rounding, which always rounds .5 up (away from 0). The Round() function in Visual Basic for Applications 6, uses Banker's rounding, which rounds .5 either up or down, whichever will result in an even number."

    See article for more info. For a VERY detailed explanation of various rounding methodologies, and how to implement your own custom rounding functions (with code samples), see MSKB Art 196652:

    HOWTO: Implement Custom Rounding Procedures

    HTH

  4. #4
    3 Star Lounger
    Join Date
    Apr 2003
    Location
    Calgary, Alberta
    Posts
    327
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Round Not Happening Correctly (Access 2000)

    Oh my, I honestly think that is one of the dumbest things I have seen. But anyway, back to my problem. How can I do a simple "Arithmetic" rounding is a query?

  5. #5
    2 Star Lounger
    Join Date
    Sep 2001
    Location
    Wellington, Wellington, New Zealand
    Posts
    181
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Round Not Happening Correctly (Access 2000)

    Hi

    Would the following work??

    Round(Amount+.000499,2)

    Regards
    WTH

  6. #6
    3 Star Lounger
    Join Date
    Apr 2003
    Location
    Calgary, Alberta
    Posts
    327
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Round Not Happening Correctly (Access 2000)

    nope

  7. #7
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Round Not Happening Correctly (Access 2000)

    A way around this is to create your own custom rounding function that does what you want.

    <pre>Function fnmyRound(mynumber As Single, places As Integer) As Single
    ' a function that rounds off a single to specified dec places, and rounds 5 down
    Dim mynewnumber As Single

    mynewnumber = mynumber * (10 ^ places)
    If mynewnumber Mod 1 <= 0.5 Then
    mynewnumber = mynewnumber 1
    Else
    mynewnumber = mynewnumber 1 + 1
    End If

    mynewnumber = mynewnumber / (10 ^ places)
    fnmyRound = mynewnumber
    End Function
    </pre>

    Attached Images Attached Images
    Regards
    John



  8. #8
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Round Not Happening Correctly (Access 2000)

    Looking at the custom Rounding functions provided in MSKB article 196652, I think this one is closest to what you want (modified from example in article):

    Function RoundSymUp(ByVal Num As Double, _
    Optional ByVal Factor As Double = 1) As Double

    Dim dblTemp As Double

    dblTemp = Fix(Num * Factor)
    RoundSymUp = (dblTemp + IIf(Num = dblTemp, 0, Sgn(Num))) / Factor

    End Function

    This is the "SymUp" rounding function, which "symmetrically rounds fractions up." Example of use:

    ? RoundSymUp(1.05,10)
    1.1
    ? RoundSymUp(1.15,10)
    1.2
    ? RoundSymUp(1.25,10)
    1.3
    ? RoundSymUp(1.35,10)
    1.4

    ? RoundSymUp(1.015,100)
    1.02
    ? RoundSymUp(1.025,100)
    1.03
    ? RoundSymUp(1.035,100)
    1.04

    ? RoundSymUp(-1.05,10)
    -1.1
    ? RoundSymUp(-1.15,10)
    -1.2
    ? RoundSymUp(-1.25,10)
    -1.3

    Note optional Factor argument which specifies precision of rounding. Article explains: "If the factor is omitted, then the functions return an integer created by one of the above methods. If the factor is specified, the number is scaled by the factor to create different rounding effects. For example AsymArith(2.55, 10) produces 2.6, that is, it rounds to 1/factor = 1/10 = 0.1." If you specify a Factor of 0, a division-by-zero error will result. For more details refer to MSKB 196652.

    HTH

  9. #9
    3 Star Lounger
    Join Date
    Apr 2003
    Location
    Calgary, Alberta
    Posts
    327
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Round Not Happening Correctly (Access 2000)

    OK, that works great. the only thing is how can I round properly in my querydef?

  10. #10
    3 Star Lounger
    Join Date
    May 2002
    Location
    Toronto, Ontario, Canada
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Round Not Happening Correctly (Access 2000)

    Just call the function in your field definition...
    Say you want a field called "fldAmount" rounded...
    In your query def just use ... fldRndAmount: RoundSymUp(fldAmount)
    Or if you use the factor... fldRndAmount: RoundSymUp(fldAmount, 10)

    HTH

  11. #11
    3 Star Lounger
    Join Date
    Apr 2003
    Location
    Calgary, Alberta
    Posts
    327
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Round Not Happening Correctly (Access 2000)

    Hi

    Looking at your solution still will round 12.255 to 12.25, even if I change the line
    If mynewnumber Mod 1 < 0.5 Then

    Is there something that I am missing. Basically, if it is .05 or.15 and above, it should round up.

    Thanks

  12. #12
    3 Star Lounger
    Join Date
    Apr 2003
    Location
    Calgary, Alberta
    Posts
    327
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Round Not Happening Correctly (Access 2000)

    OK, here is a way that works how the rounding is supposed to happen (for me anyways). It is a slighly modifed version for Microsoft

    Function RoundToNearest(dblNumber As Double, varRoundAmount As Double, _
    Optional varUp As Variant) As Double

    Dim dblTemp As Double
    Dim lngTemp As Long

    dblTemp = dblNumber / varRoundAmount
    lngTemp = CLng(dblTemp)

    If lngTemp = dblTemp Then
    RoundToNearest = dblNumber
    Else
    If (dblTemp - lngTemp) < 0.5 Then
    'If IsMissing(varUp) Then
    ' round down
    dblTemp = lngTemp
    Else
    ' round up
    dblTemp = lngTemp + 1
    End If
    RoundToNearest = dblTemp * varRoundAmount
    End If
    End Function

    Now I have to make sure it is applied everywhere [img]/forums/images/smilies/sad.gif[/img]

    Thanks for the help everyone

Posting Permissions

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