Answered by:
Problem with currency and rounding
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,6All 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