calculated field problem

[tuskan]

Hi folks,
Amount: [UnitPrice]*[Quantity]*(1-[Discount])
I have typed this formula in a query in northwind.mdb from the Order
details table.
What i dont really get is actually that let's say i have
Unit Price: 2
Quantity: 50
Discount: 10%
Amount should be 90 (i guess) but the result when i run the query is
89,9999998509884
What is the problem with the floating points, any help is appreciated.
Thanks

 

[Marshall Barton]

Amount: [UnitPrice]*[Quantity]*(1-[Discount])
I have typed this formula in a query in northwind.mdb from the Order
details table.
What i dont really get is actually that let's say i have
Unit Price: 2
Quantity: 50
Discount: 10%
Amount should be 90 (i guess) but the result when i run the query is
89,9999998509884
What is the problem with the floating points,

Nothing is wrong with floating point numbers other than the
fact that they are not precise for many values. This is the
same problem you would have if you tried to write down the
decimal value of 1/3. The binary value of .1 has exactly
the same issue.

If you just round the result of your calculation to 2
places, everything should look fine.
Amount: Round([UnitPrice]*[Quantity]*(1-[Discount]), 2)

 

[tuskan]

Thanks for your kind response, what i still dont get is:
if i have used the same formula in Excel i wouldnt use the round
function, thats why i am a little bit confused.
what is the calculation logic difference between excel and access when
it comes to calculation of decimals like Discount * UnitPrice *
Quantity? that forces me to use the round function in one of them? to
get the correct result? i tried in Excel Amount:
[UnitPrice]*[Quantity]*[Discount] with the numbers 100 * 2 * 5% = 10
in Access the result is:
Amount: [UnitPrice]*[Quantity]*[Discount] with the numbers 100 * 2 *
5% = 10,0000001490116
i would like to have an explanation about this floating ,0000001490116
if possible pls.
Thanks in advance

 

[Marshall Barton]

I don't know what Excel is doing to present the exact value.
It could be doing the rounding for you when it displays the
result of the calculation or maybe you used a format with
only 2 places (Access will do the same in many situations).
Try using this expression in an Excel cell:
=UnitPrice]*[Quantity]*[Discount] = 10
and see if the cell displays True or False.

Another possibility is to use the Currency (or Decimal) data
type, which is a fixed point type number with 4 places of
accuracy. (Note: the Decimal data type in Access can be
difficult to use)

For any floating point type, you can be sure that the result
needs to rounded somehow somewhere because the value .1
requires an infinite number of bits.
--
Marsh
MVP [MS Access]

Thanks for your kind response, what i still dont get is:
if i have used the same formula in Excel i wouldnt use the round
function, thats why i am a little bit confused.
what is the calculation logic difference between excel and access when
it comes to calculation of decimals like Discount * UnitPrice *
Quantity? that forces me to use the round function in one of them? to
get the correct result? i tried in Excel Amount:
[UnitPrice]*[Quantity]*[Discount] with the numbers 100 * 2 * 5% = 10
in Access the result is:
Amount: [UnitPrice]*[Quantity]*[Discount] with the numbers 100 * 2 *
5% = 10,0000001490116
i would like to have an explanation about this floating ,0000001490116
if possible pls.
Thanks in advance

Nothing is wrong with floating point numbers other than the
fact that they are not precise for many values. This is the
same problem you would have if you tried to write down the
decimal value of 1/3. The binary value of .1 has exactly
the same issue.

If you just round the result of your calculation to 2
places, everything should look fine.
Amount: Round([UnitPrice]*[Quantity]*(1-[Discount]), 2)