Results 1 to 8 of 8
  1. #1
    Join Date
    Oct 2003
    Location
    Chicago, IL
    Posts
    154

    Unanswered: Rounding issue - off by 1

    Hi,

    I think this is the oldest, most tired question asked on the forum, but since I never asked it and I couldn't find the answer, here's my stab! The perpetual rounding question:

    I have a Query that has the following field:

    ExtendedPrice: CCur(tblOrderDetails.UnitPrice*[Quantity]*(1-[Discount])/100)*100

    Problem:
    if the [unitprice] value is $144.88
    the [ExtendedPrice] value might be $144.87

    Any ideas how I can eliminate the variance other than to have the user manually edit the field?!

    Thanks in advance for your help with this...

  2. #2
    Join Date
    Nov 2003
    Posts
    1,487
    I thought the world was gonna get rid of the damn penny

    I dunno...just a thought...make sure all your fields, Table and Form, containing currency has its decimal places set to either 2 or 3 (whatever your choice). You know....all the same. Not set to Auto. You may want to format your calculations that way to.

    .
    Environment:
    Self Taught In ALL Environments.....And It Shows!


  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Store currency using the correct data type and to solve rounding issues with 2dp values, take a look at the Fix() function (sometimes referred to as ceiling() or floor())
    George
    Home | Blog

  4. #4
    Join Date
    Oct 2003
    Location
    Chicago, IL
    Posts
    154
    Well, I ended up not having to do anything - As crazy as it probably would make most of us, the client stated that he did not care! Agg, not my desired outcome but if he's happy, I'm happy.

    Thanks for the suggestions, guys. Sorry to come back to you all with a useless reply!

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Just an afterthought...
    Code:
    CCur((tblOrderDetails.UnitPrice*[Quantity]*(1-[Discount])/100)*100)
    ...Would that work?
    George
    Home | Blog

  6. #6
    Join Date
    Nov 2002
    Posts
    272
    Maybe it helps to change the order of the calculations, and George's brackets:
    CCur((tblOrderDetails.UnitPrice*[Quantity]*100*(1-[Discount])/100))

  7. #7
    Join Date
    Jun 2007
    Posts
    33
    With round function

    CCur(Round((tblOrderDetails.UnitPrice*[Quantity]*100*(1-[Discount])/100)),2)

    or

    CCur(Round((Round((tblOrderDetails.UnitPrice*[Quantity]),2)*100*(1-[Discount])/100)),2)

  8. #8
    Join Date
    Jul 2003
    Location
    Amsterdam, Nederland
    Posts
    450

    other 1

    What you can do is the following

    say your amount after calculation is 11,5944
    allways add up 0,0005
    then use the round function or your /100
    so in ur case

    CCur((tblOrderDetails.UnitPrice*[Quantity] *(1-[Discount])/100) + 0.0005) *100

Posting Permissions

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