Prev: Joins on NULL Date values
Next: Blank Fields in NOT Null Query Criteria
From: The Dude on 27 May 2010 16:45
Hi all!

I am running a query in a subform where I sum a [number of units] *
[operation sign]. Units are imported from a csv file with 3 decimals and
operation sign is either -1 or +1:
For example, [440.000*-1] + [550.070*+1]... etc...

For a strange reason, Access shows 7 to 9 decimals in the sum's result. Now
they are not zeros and it seems like it's calculating on something but I
can't figure what!
The problem is that the result is wrong, and the only way I can get a good
result is by formating like "#,##0.000" the product of the two.

This is just insane and I can't understand why it would do that...

Any thougts?
Thx
T_D
From: John Spencer on 28 May 2010 11:46
Floating point numbers can act like that. The problem is that the fractional
portion of a number often cannot be accurately represented. For instance, in
a base 10 system there is no way to completely resolve 1/3. Similiarly, in a
binary system there are fractions that cannot be fully resolved. Now throw in
the complication of converting between the two systems - binary and decimal
and fractional portions of numbers introduce slight differences.

Since you are accurate to 3 decimal points try using CCur to cast the results
into the special fixed decimal (4 places after the decimal) that currency
represents.

CCUR([Number of Units]) * CCur([Operation Sign])

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

The Dude wrote:
> Hi all!
>
> I am running a query in a subform where I sum a [number of units] *
> [operation sign]. Units are imported from a csv file with 3 decimals and
> operation sign is either -1 or +1:
> For example, [440.000*-1] + [550.070*+1]... etc...
>
> For a strange reason, Access shows 7 to 9 decimals in the sum's result. Now
> they are not zeros and it seems like it's calculating on something but I
> can't figure what!
> The problem is that the result is wrong, and the only way I can get a good
> result is by formating like "#,##0.000" the product of the two.
>
> This is just insane and I can't understand why it would do that...
>
> Any thougts?
> Thx
> T_D
 | 
Pages: 1
Prev: Joins on NULL Date values
Next: Blank Fields in NOT Null Query Criteria