Negative zero result from decimal calculations

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Was getting some interesting results coming out of some calculations in
access. To use a specific example, had the numbers 1.75 and 1.7.
Subtracting the two yielded .04999999999999. Displaying the Rounded numbers
later in a report then showed that 1.8 minus 1.7 = 0. Not ideal.

I fixed this by using a custom function based on the CDec vb function. Now
1.75 - 1.7 equals 0.05. Hurrah!

Unfortunately, 1.7-1.7 now seems to equal -0. Not 0. -0. Well, I should
clarify, it looks like 0 in the query, but when it is exported or accessed
elsewhere (i.e. excel) its passing through a -0. CDbl on the result yields
an Error. nz works, but changes blanks/nulls to zero as well, which I don't
want in this case. Any suggestions or insights?
 
Hi cmpf,

What custom function did you use?

Better to use CDbl rather than CDec due to the inherent problems with
decimal data types - just search in here for plenty of examples.

TonyT..
 
cmpf said:
I fixed this by using a custom function based on the CDec vb function. Now
1.75 - 1.7 equals 0.05. Hurrah!

Unfortunately, 1.7-1.7 now seems to equal -0. Not 0. -0. Well, I should
clarify, it looks like 0 in the query, but when it is exported or accessed
elsewhere (i.e. excel) its passing through a -0.
Click to expand...

You shouldn't need the CDEC function for the calculation 1.7 - 1.7
because native decimal values in Jet 4.0 SQL code are 'seen' as being
data of the DECIMAL type e.g.

SELECT TYPENAME(1.7)

returns 'Decimal'.

The whole point of having the DECIMAL type is to avoid inaccuracies
i.e.

SELECT 1.7 - 1.7

will always return zero in Jet 4.0.

I have not been able to reproduce -0 at all, without resorting to
something like this:

SELECT IIF(-0.17 < 0, '-', '') & FORMAT(-0.17, '0')

Please post some instructions on how to reproduce -0.

TIA,
Jamie.

--
 
Back
Top