none
If a price is 6,665 then put 6,67 and not 6,66

    Question

  • Good afternoon,

    In my "Prix unitaire" field, I would like to have the 3 decimals to go to next dollar amount.

    Example: 6,665 would show 6,67 $ and 6,664 would show 6,66 $

    Here is the formula for the price:

    Prix total vente1: ([PrixVente1]*[Quantité])

    Thank you all


    Claude Larocque

    Wednesday, August 28, 2013 4:59 PM

Answers

  • I'm not sure from your description which field it is that you are really wanting to round.  However, you should be aware that the Round() function that is built into Access uses "banker's rounding", which rounds a 5 up when the preceding decimal place is odd, and down when the preceding decimal place is even.  If you want to avoid that, you have to use your own custom rounding function.  There's one written by Dev Ashish posted on The Access Web here:

        http://access.mvps.org/access/modules/mdl0054.htm
        Modules: Implementing a custom Rounding procedure


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html


    Wednesday, August 28, 2013 5:25 PM
  • Good afternoon,

    In my "Prix unitaire" field, I would like to have the 3 decimals to go to next dollar amount.

    Example: 6,665 would show 6,67 $ and 6,664 would show 6,66 $

    Hi Claude,

    The rounding in Access is the Bankers rounding. It means that half a cent is rounded to the nearest EVEN number.

    6,665 is rounded to 6,66, and 6,675 is rounded to 6,68.

    When you want to always round up, that is 6,665 to 6,67 and 6,675 to 6,68, then you can add a small amount, for instance 0,0001, so that the exactly 0,005 becomes more then 0,005: 0,0051.

    There is also a pitfall in this case, for instance if you want to round 6,6749. Without the 0,0001 it rounds to 6,67, with the 0,0001 it rounds to 6,68. But perhaps this is very exceptional for your normal cases. But in cases it would give a problem, just take a smaller increment: 0,000001 or 0,000000001.

    Imb.

    • Proposed as answer by Van DinhMVP Wednesday, August 28, 2013 10:26 PM
    • Marked as answer by Claude Larocque Thursday, August 29, 2013 10:15 AM
    Wednesday, August 28, 2013 5:43 PM
  • You could make a Reference in the VBE to Excel and use the WorksheetFunction called RoundUp.

    Dev Ashish has an elegant solution but not very efficient. For instance, I wouldn't want to be calling it from SQL in a udf function against 100,000 rows of data.

    RoundUp in Excel is pure C/C++ and likely 10-100x more efficient.

    Wednesday, August 28, 2013 8:14 PM

All replies

  • I'm not sure from your description which field it is that you are really wanting to round.  However, you should be aware that the Round() function that is built into Access uses "banker's rounding", which rounds a 5 up when the preceding decimal place is odd, and down when the preceding decimal place is even.  If you want to avoid that, you have to use your own custom rounding function.  There's one written by Dev Ashish posted on The Access Web here:

        http://access.mvps.org/access/modules/mdl0054.htm
        Modules: Implementing a custom Rounding procedure


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html


    Wednesday, August 28, 2013 5:25 PM
  • Good afternoon,

    In my "Prix unitaire" field, I would like to have the 3 decimals to go to next dollar amount.

    Example: 6,665 would show 6,67 $ and 6,664 would show 6,66 $

    Hi Claude,

    The rounding in Access is the Bankers rounding. It means that half a cent is rounded to the nearest EVEN number.

    6,665 is rounded to 6,66, and 6,675 is rounded to 6,68.

    When you want to always round up, that is 6,665 to 6,67 and 6,675 to 6,68, then you can add a small amount, for instance 0,0001, so that the exactly 0,005 becomes more then 0,005: 0,0051.

    There is also a pitfall in this case, for instance if you want to round 6,6749. Without the 0,0001 it rounds to 6,67, with the 0,0001 it rounds to 6,68. But perhaps this is very exceptional for your normal cases. But in cases it would give a problem, just take a smaller increment: 0,000001 or 0,000000001.

    Imb.

    • Proposed as answer by Van DinhMVP Wednesday, August 28, 2013 10:26 PM
    • Marked as answer by Claude Larocque Thursday, August 29, 2013 10:15 AM
    Wednesday, August 28, 2013 5:43 PM
  • You could make a Reference in the VBE to Excel and use the WorksheetFunction called RoundUp.

    Dev Ashish has an elegant solution but not very efficient. For instance, I wouldn't want to be calling it from SQL in a udf function against 100,000 rows of data.

    RoundUp in Excel is pure C/C++ and likely 10-100x more efficient.

    Wednesday, August 28, 2013 8:14 PM
  • Thank you all for your responses,

    The response I use belongs to Imb and adding 0.0001 to the formula, seems to be the easiest way because it is calculating in the field without having to link to an Excel worksheet or go through 100,000 lines of data....

    Thanks Imb

    I have also evaluated Dev Ashish solution but in an SQL takes times...

    Have a great day all of you

    Claude


    Claude Larocque

    Thursday, August 29, 2013 10:14 AM