none
Problem with currency and rounding RRS feed

  • Question

  • Hi

    Having av Query1 one that gives the following values presented in Query2

    Query2

    Quantity

    Prize

    40

    21,995

     

    Then I pick the format Curreny for the Prize in Query2 and get the wanted rounded Prize

    Query2

    Quantity

    Prize

    40

    22,00 kr

     

    But when I want to have a Total in Query3 the results is:

    Query3

    Quantity

    Prize

    TOTAL

    40

    22,00 kr

    879,8

     

    SELECT Query2.Quantity, Query2.Prize, [quantity]*[Prize] AS TOTAL

    FROM Query2;

     

    And of coarse it should be 880. What can be done to get the wanted figures?


    Best // Peter Forss Stockholm and Sigtuna GMT +1.00

    Tuesday, March 8, 2016 6:50 AM

Answers

  • Either ensure that Prize contains 22.00o instead of 21.995, or change the expression for Total to

    [Quantity]*Round([Prize],2)


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Proposed as answer by André Santo Tuesday, March 8, 2016 1:06 PM
    • Marked as answer by ForssPeterNova Saturday, March 19, 2016 5:57 AM
    Tuesday, March 8, 2016 6:53 AM
  • And of coarse it should be 880. What can be done to get the wanted figures?

    Hi Peter,

    When working with monetary values, always use the Currency type. The Currency rounds automatically to 4 decimals, so you get rid of the natural inaccuracy of the Single or Double type. In normal cases you then do not need to round anymore to get your "exact" cents.

    But be careful! When you have a long list of values that must be added, ans your running variable is of the Single or Double type, then the result after converting to the Currency type, could be something like    23456789.0001. In that case you need an additional rounding to two decimals for "exact" cents.

    In calculations, especially division or fractional multiplications (VAT, BTW, ...) you need also a rounding to two decimals to work "exact". The moment where you do the rounding is also important for consistent results.

    In your above example, the figures on the screen are display values.  The real value is 21.995, that is only displayed as 22.00. This can be corrected by a proper rounding, as Hans also suggested.

    Imb.

    • Proposed as answer by André Santo Tuesday, March 8, 2016 1:06 PM
    • Marked as answer by ForssPeterNova Saturday, March 19, 2016 5:57 AM
    Tuesday, March 8, 2016 7:17 AM

All replies

  • Either ensure that Prize contains 22.00o instead of 21.995, or change the expression for Total to

    [Quantity]*Round([Prize],2)


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Proposed as answer by André Santo Tuesday, March 8, 2016 1:06 PM
    • Marked as answer by ForssPeterNova Saturday, March 19, 2016 5:57 AM
    Tuesday, March 8, 2016 6:53 AM
  • And of coarse it should be 880. What can be done to get the wanted figures?

    Hi Peter,

    When working with monetary values, always use the Currency type. The Currency rounds automatically to 4 decimals, so you get rid of the natural inaccuracy of the Single or Double type. In normal cases you then do not need to round anymore to get your "exact" cents.

    But be careful! When you have a long list of values that must be added, ans your running variable is of the Single or Double type, then the result after converting to the Currency type, could be something like    23456789.0001. In that case you need an additional rounding to two decimals for "exact" cents.

    In calculations, especially division or fractional multiplications (VAT, BTW, ...) you need also a rounding to two decimals to work "exact". The moment where you do the rounding is also important for consistent results.

    In your above example, the figures on the screen are display values.  The real value is 21.995, that is only displayed as 22.00. This can be corrected by a proper rounding, as Hans also suggested.

    Imb.

    • Proposed as answer by André Santo Tuesday, March 8, 2016 1:06 PM
    • Marked as answer by ForssPeterNova Saturday, March 19, 2016 5:57 AM
    Tuesday, March 8, 2016 7:17 AM
  • Still having issues on rounding and currency

     

    SELECT Query2.Quantity, Query2.Prize, [quantity]*Round([Prize],1) AS [TOTAL ;1], [quantity]*Round([Prize],2) AS [TOTAL ;2], Round([Prize],1) AS [P ;1], Round([Prize],2) AS [P ;2]

    FROM Query2;

    Example with a Prize shown as 21,44

    Query3

    Quantity

    Prize

    TOTAL ;1

    TOTAL ;2

    P ;1

    P ;2

    40

    21,44 kr

    856

    857,6

    21,4

    21,44

    40 x 21,44 = 857,6 ( column four above is right)

    Example with a Prize shown as 22,00

    Query3

    Quantity

    Prize

    TOTAL ;1

    TOTAL ;2

    P ;1

    P ;2

    40

    22,00 kr

    880

    879,6

    22

    21,99

    40 x 22,00 = 880 (column three above is right)

    Same SQL used:

    SELECT Query2.Quantity, Query2.Prize, [quantity]*Round([Prize],1) AS [TOTAL ;1], [quantity]*Round([Prize],2) AS [TOTAL ;2], Round([Prize],1) AS [P ;1], Round([Prize],2) AS [P ;2]

    FROM Query2;

    So task is to have Query2 to really deliver a figure with two decimals and not only show it. But I really can’t make it

    Query2

    Quantity

    Prize

    Pris

    Query1Prize

    40

    22,00 kr

    21,99

    21,995

     

    SELECT Query1.Quantity, Query1.Prize, Round([Prize],2) AS Pris, Query1.Prize AS Query1Prize

    FROM Query1

    GROUP BY Query1.Quantity, Query1.Prize, Round([Prize],2), Query1.Prize;


     

     


    Best // Peter Forss Stockholm and Sigtuna GMT +1.00

    Tuesday, March 8, 2016 2:51 PM
  • Example with a Prize shown as 22,00

    Query3

    Quantity

    Prize

    TOTAL ;1

    TOTAL ;2

    P ;1

    P ;2

    40

    22,00 kr

    880

    879,6

    22

    21,99

    40 x 22,00 = 880 (column three above is right)

    Same SQL used:

    SELECT Query2.Quantity, Query2.Prize, [quantity]*Round([Prize],1) AS [TOTAL ;1], [quantity]*Round([Prize],2) AS [TOTAL ;2], Round([Prize],1) AS [P ;1], Round([Prize],2) AS [P ;2]

    FROM Query2;

    So task is to have Query2 to really deliver a figure with two decimals and not only show it. But I really can’t make it

    Query2

    Quantity

    Prize

    Pris

    Query1Prize

    40

    22,00 kr

    21,99

    21,995

     

    SELECT Query1.Quantity, Query1.Prize, Round([Prize],2) AS Pris, Query1.Prize AS Query1Prize

    FROM Query1

    GROUP BY Query1.Quantity, Query1.Prize, Round([Prize],2), Query1.Prize;

    Hi Peter,

    The price shown is 22,00 kr, but in the table it is stored as 21,995

    Round(price,1) = 22,0
    Round(price,2) = 21,99
    40 * Round(price,1) = 880
    40 * Round(price,2) = 879,6

    All seems correct to me.

    What you probably want, is to do first the multiplication, then the rounding:   40 * 21.995 = 879.6 and Round(879.6,2) = 879,60

    Or perhaps you do not want 21.995 as price, then change it in the table to 22.

    Imb.

    Tuesday, March 8, 2016 4:18 PM
  • If the price is rounded to 21.99, its unrounded value must be slightly less than 21.995, for example 21.9949. If it were exactly 21.995, it would be rounded to 22.00.

    So the problem is not in the rounding, but in your data. You'll have to ensure that they are correct...


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Tuesday, March 8, 2016 4:22 PM