formula challenge

[Freefall27]

Working in Access 2000 and experiencing a calculation issue when running query.

Quantity: [Field]/9.2 - 9.2 used as a billing rate
IntQuantity: Int([Quantity])
Whole Quantity: [quantity]-[intquantity]

If I remove the /9.2 the calculation is correct.

When using the /9.2 it gives the wrong answer in some cases as shown below.

Ex.
Quantity = 6 (55.2/9.2)
IntQuantity = 6
Whole Quantity = 8.88178419700125E-16 (This should be 0)

I think the question and examples are ample to explain the issue but please let me know if you need more detail.

 

[jsteph]

It looks like a rounding/precision issue to me. When dividing by a float you'll get a float as a result and zero might be .000000000000000888178, which is what it looks like here.

To avoid this, pre-scale to longint if the values will fit, or use Currency data type, which is a double-long integer (not a float).
--Jim

 

[MajP]

To add to what Jim said this is not a Access problem or VBA problem, but a limitation of of how all computers handle numbers other than integer types.

http://en.wikipedia.org/wiki/Floating_point

Computers represent every number (and everything) by a sequence of ones and zeros, and with arithmetic the limitations become apparent.

Using the Currency data type as Jim says is a great option if if working with currency. In code and some dbs, there is a decimal data type. Or you can format or round your data.

In code you normally do not do
if WholeQuantity = 0
but
if WholeQuantity < .0000000000000001 then