Multiplication Error in Query (1 Viewer)

freidaf · Feb 6, 2018

Hello,
I am trying to multiply 152 by .94 in a calculated field in my query to get the total cost. The query returns $142.76 but the correct answer is $142.88.

I have read that it is a rounding issue but I can't understand how to correct it. Any help would be appreciated.

Thank you

CJ_London · Feb 6, 2018

are you sure the underlying values are exactly as stated and have not been formatted to zero and 2 dp respectively? what happens if you use the formula

round(152value,0)*(round(.94value,2)

or in your query set the format property for each value to say 4 dp, what do you see?

freidaf · Feb 7, 2018

OMG! You solved my problem. Thank you so much. I can leave work now with a clear head!

CJ_London · Feb 7, 2018

always better to leave any formatting around rounding etc off until you are presenting data in a form or report. Or if values have to be rounded (e.g. decimal quantity x a price or a tax rate) should be rounded at the time of calculation.

Galaxiom · Feb 7, 2018

BTW When calculating money or any other numbers with decimal places where precision matters, always be sure to use Currency or Decimal data type. Currency is good for four decimal places and Decimal for up to 18.

Single and Double datatypes don't always calculate precisely due to the difficulty of storing fractional numbers in binary.

Try this expression in the immediate window for a surprising answer:

Code:

? Int(0.7 * 90)

For the correct answer:

Code:

? Int(CDec(0.7) * 90)

freidaf · Feb 13, 2018

Thank you for the helpful advice!

Multiplication Error in Query (1 Viewer)

freidaf

Registered User.

CJ_London

Super Moderator

freidaf

Registered User.

CJ_London

Super Moderator

Galaxiom

Super Moderator

freidaf

Registered User.

Similar threads

Users who are viewing this thread