Results 1 to 12 of 12
  1. #1
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Rounding up not wanted (2002)

    Hi,

    I have a report that is based on a variety of queries filled with formulas. I've set the formats as well as the table that holds the data to currency with 2 decimal places. Everything is working fine with the exception that the the actual data goes to 4 decimal places (I don't know how to change this) and so bill totals are rounding up giving incorrect totals. For example a bill with totals of $778.05, $293.29+ $850 + $100 should have a total of $2021.34 for instead is returning $2021.35. It doesn't matter whether I obtain the total in the query and put it on the report or whether I have an unbound text box and do the adding in that. The result is always 1 cent higher. Is there a way to fix this? I've already made sure each query is set to 2 decimals but still no fix.

    Thanks,
    Leesha

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

    Re: Rounding up not wanted (2002)

    Are (some of) those amounts calculated values?

  3. #3
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Rounding up not wanted (2002)

    Yes they are.

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

    Re: Rounding up not wanted (2002)

    Say that you have a calculated value
    <code>
    A: <!t>[B]<!/t>*[C]
    </code>
    To make sure the result is rounded to 2 decimal places, change it to
    <code>
    A: Round(<!t>[B]<!/t>*[C],2)
    </code>
    If you use A in an addition, it won't cause rounding up.

  5. #5
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Rounding up not wanted (2002)

    OMG Hans, you just saved my life!!!! Thank you so much. You don't even want to know how long I worked at this and looked for and answer. There needs to be "cheat sheet" on how to find help in access.

    Thanks !,
    Leesha

  6. #6
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Rounding up not wanted (2002)

    I've noticed one thing I'd like to fix if possible. I have the following SQL in a query

    DebitMsgBox: IIf([debit account]=True,"Your account will be debited AFTER the 5th of the month for $" & "" & [totalamountdue] & "" & ".")

    It works fine and returns on the bill what I need when I need it with the exception that after I used the rounding suggestion on [totalamountbilled], this expression drops any of the zeros at the end of the expression. For example $213.90 becomes 213.9. I'm pretty sure its because its not formatted here as currency but I wasn't sure where to do that in the expression, or if it was even possible.

    Leesha

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

    Re: Rounding up not wanted (2002)

    Try

    DebitMsgBox: IIf([debit account],"Your account will be debited AFTER the 5th of the month for $" & Format([totalamountdue],"0.00") & ".")

    (I removed the superfluous empty strings "")

  8. #8
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Rounding up not wanted (2002)

    Perfect! I was thrilled with myself that I even got it that far and figured out the possible cause of the problem, but figuring out that formatting piece for the currency was beyond me.

    Thanks!
    Leesha

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

    Re: Rounding up not wanted (2002)

    >>I've already made sure each query is set to 2 decimals but still no fix.<<

    I see Hans already solved your problem, but I wanted to add just a little something extra. Setting the decimal property (whether in a query, a table, or a form) merely affects the display of your data, and not the data itself. That formula for rounding that Hans gave you is very valuable, and you should get in the habit of using it frequently.

    For example, in the AfterUpdate event of any textbox that I want to restrict the data to 2 decimal places, I just put this:
    txtCurrentField = round(txtCurrencyField,2)

    Any time I do a calculation, I round:
    txtTaxAmount = round(txtInvoiceTotal*txtTaxRate,2)

    Just get in the habit of doing it, and you (or your client) won't go crazy trying to figure out why an Invoice Amount of $100.00 still says "Unpaid" when there has been a payment of $100.00! (This is because the Invoice amount is actually $100.0001!)
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  10. #10
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Rounding up not wanted (2002)

    Thanks for the tip. I will certainly make a habit of this from now on!

    As always, I can't imagine life without this forum!

    Leesha

  11. #11
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Rounding up not wanted (2002)

    Another tidbit I learned recently at a DAAUG meeting is that the intrinsic functions found in recent versions of Access use what is called Banker's Rounding. You can find out more about the issue in <!mskb=196652>Microsoft Knowledge Base Article 196652<!/mskb> and <!mskb=194983>Microsoft Knowledge Base Article 194983<!/mskb>.
    Wendell

  12. #12
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Rounding up not wanted (2002)

    Thanks Wendell! I'll read up on it!

    Leesha

Posting Permissions

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