Problem in datatype in calculation

[Keith]

I have a UnitPrice field and a Quantity field both of Single Type.
I'm totally confused and frustrated with the different results I get when I
multiply these two fields to get a TotalValue.
When I have a Quantity of 4,032,000 multiply by a Unit Price of 0.03728,
instead of getting an exact amount of 150,312.96, I get 150,312.96875.
When I tried CDbl(UnitPrice) * CDbl(Quantity), I get 150,312.963009. This is
still acceptable as I'll only keep 2 decimal points in the TotalValue. But
when I use this conversion, I had problem with another set of figures. This
time when I use a Quantity of 165,200 multiply by a UnitPrice of 1.065, I get
175,938.009453 which is wrong if I round it off to 2 decimal places.
I really hope there's help asap and really appreciate the help

Thanks & Regards,
Keith

 

[John Vinson]

I have a UnitPrice field and a Quantity field both of Single Type.
I'm totally confused and frustrated with the different results I get when I
multiply these two fields to get a TotalValue.
When I have a Quantity of 4,032,000 multiply by a Unit Price of 0.03728,
instead of getting an exact amount of 150,312.96, I get 150,312.96875.
When I tried CDbl(UnitPrice) * CDbl(Quantity), I get 150,312.963009. This is
still acceptable as I'll only keep 2 decimal points in the TotalValue. But
when I use this conversion, I had problem with another set of figures. This
time when I use a Quantity of 165,200 multiply by a UnitPrice of 1.065, I get
175,938.009453 which is wrong if I round it off to 2 decimal places.
I really hope there's help asap and really appreciate the help

Thanks & Regards,
Keith

Single and Double are both "Floating Point" numbers. They're stored as
a binary fraction times an exponent. Single is accurate to about seven
decimal places (so your first result makes sense); Double is accurate
to about 14 places. Neither is exact.

The solution is to not use ANY "Number" datatype. Instead, use a
Currency datatype field, and (if need be) CCur() to convert the value
to Currency. A Currency datatype is accurate to exactly four decimals,
with no roundoff error, and a range into the trillions.

John W. Vinson[MVP]

 

[Ofer]

try
round([Quantity] * [Unit Price] ,2)

The 2 specify two decimal places

 

[Keith]

Hi thanks for replying. It did not solve the problem entirely but was really
close.
I got this instead of 150,392.96
4,032,000 * 0.03728 = 150,393.6
It seems tat it rounds off to 3 decimal places for currency data type.

Thanks,
Keith

 

[John Vinson]

Hi thanks for replying. It did not solve the problem entirely but was really
close.
I got this instead of 150,392.96
4,032,000 * 0.03728 = 150,393.6
It seems tat it rounds off to 3 decimal places for currency data type.

It's rounding off to four, and exactly four - just as I said in my
message. 0.03728 is being rounded to 0.0373.

Try 4,032,000 * 3.728 / 100.

John W. Vinson[MVP]

 

[Keith]

Hi John! I've managed to solve it finally using both of you suggestions.
I used (Quantity * CCur(UnitPrice * 100)) / 100 and all calculations are
perfect!
Thanks alot for ur help!