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

Answer 1

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)

Answer 2

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.

Answer 3

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

Answer 4

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.

Answer 5

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)