Results 1 to 14 of 14
  1. #1
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Help With Rounding? ((a2k (9.0.6926) SP-3 Jet 4.0 SP-8) )

    I have an Append Query with the following expression in one of the columns:

    TownAmount: Round(Int([Fee]*53)/100,2)+[Local]

    All fields are currency with auto decimal places

    The resulting field in the table has the following value:

    TownAmount
    $2,033.38

    It should be $2,033.37

    When I click on the $2,033.38 value it changes to $2,033.375

    What do I have to change to get $2,033.37 as the correct value?

    Thanks, John

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Help With Rounding? ((a2k (9.0.6926) SP-3 Jet 4.0 SP-8) )

    Why should 2,033.375 be rounded to 2,033.37? The standard rule is that if the next decimal is 0, 1, 2, 3, or 4, the last displayed decimal should be rounded down; if the next decimal is 5, 6, 7, 8, or 9, the last displayed decimal should be rounded up. So rounding 2,033.375 to 2,033.38 is correct.

  3. #3
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Help With Rounding? ((a2k (9.0.6926) SP-3 Jet 4.0 SP-8) )

    Unless you're a banker, then the customers lose that $.05....ever seen the movie Office Space? Sorta like that. <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Help With Rounding? ((a2k (9.0.6926) SP-3 Jet 4.0 SP-8) )

    Perhaps it's a question of terminology: rounding can be up or down, truncating is always down (probably because the difference goes into the bank's trunks <img src=/S/grin.gif border=0 alt=grin width=15 height=15>)

    Round(2033.375, 2) = 2033.38

    Int(2033.375 * 100) / 100 = 2033.37

    In fact, Round confusingly uses a method called Banker's Rounding: 2033.375 becomes 2033.38 and 2033.365 becomes 2033.36. If the next digit is 5, the last decimal will be the nearest even digit.

  5. #5
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Help With Rounding? ((a2k (9.0.6926) SP-3 Jet 4.0 SP-8) )

    Why is $2,033.37 the "correct" value? One problem is that you round, then add to this the value in [Local]. If [Local] has 3 decimal places, then your result has 3 decimal places. You need to round AFTER you've done all the math.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  6. #6
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Help With Rounding? ((a2k (9.0.6926) SP-3 Jet 4.0 SP-8) )

    Sorry for my confusion, I

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Help With Rounding? ((a2k (9.0.6926) SP-3 Jet 4.0 SP-8) )

    John, you still haven't told us why you want 2033.375 to be rounded to 2033.37.

    If you want to truncate the end result of TownAmount to two decimal places, you should use

    TownAmount: Int(([Fee]*53+[Local])*100)/100

  8. #8
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Help With Rounding? ((a2k (9.0.6926) SP-3 Jet 4.0 SP-8) )

    I

  9. #9
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Help With Rounding? ((a2k (9.0.6926) SP-3 Jet 4.0 SP-8) )

    What happens if you change it to:

    <code>
    TownAmount: Round(Int([Fee]*53)/100+[Local],2)
    </code>
    Legare Coleman

  10. #10
    4 Star Lounger
    Join Date
    Jan 2002
    Location
    Brookings, South Dakota, USA
    Posts
    449
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Help With Rounding? ((a2k (9.0.6926) SP-3 Jet 4.0 SP-8) )

    I don't know if this will help, but Helen Feddema has an archived Access Archon (#94) on her site which deals with rounding.
    Helen Feddema's web site
    <IMG SRC=http://www.wopr.com/w3tuserpics/gdrezek_sig.jpg>
    "Those who dance are considered insane by those who can't hear the music" - George Carlin

  11. #11
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Help With Rounding? ((a2k (9.0.6926) SP-3 Jet 4.0 SP-8) )

    I never encountered that kind of rounding when I was a banker, John, but your formula doesn't make sense, since any number * 100 then divided by 100 is going to give you exactly the same number you started with! If it doesn't appear that way in the query, it's because of the way you formatted the field. <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15> Currency fields have 4 decimal places and truncate beyond that.
    Charlotte

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Help With Rounding? ((a2k (9.0.6926) SP-3 Jet 4.0 SP-8) )

    <img src=/S/whisper.gif border=0 alt=whisper width=29 height=17> Int(n * 100) / 100 is a valid way to truncate n to 2 decimal places.

  13. #13
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Help With Rounding? ((a2k (9.0.6926) SP-3 Jet 4.0 SP-8) )

    Yes, it is, but in glancing at John's initial post, I didn't look closely enough and missed the Int function in there. Mea culpa. <img src=/S/stupidme.gif border=0 alt=stupidme width=30 height=30>
    Charlotte

  14. #14
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Pennsylvania, USA
    Posts
    144
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Help With Rounding? ((a2k (9.0.6926) SP-3 Jet 4.0 SP-8) )

    It would seem that the term "Bankers Rounding" is not really used in the content of its' name. I signed myself into an Accounting/Bookkeeping Class, the instructor gave us 16 variations of the same calculation, and said these are "Bankers Rounding". Almost had me believing that 2+2=3.

    If someone wants to do some creative math they call it "Bankers Rounding".

    Some folks round up and some folks round down. Some round to the nearest number.

    As a general rule we ( I Believe ) accept that .564 would round to .56 and .566 would round to .57 and .565 would and can go either way up or down.

    This becomes' a night mare at times when you have to deal with it, you are assured to be off by .01.

    For what its' worth
    threecrow

    Don&#39;t make excuses. It&#39;s what you do, not why you didn&#39;t.

Posting Permissions

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