Rounding for number "Single" filed problem

[Daryl]

Dear All,

I have a lots of trouble of rounding the number (single). Can somebody help.
I have declared a "Single" field and input some numbers for example 12.23,
1.99, 0.99 ...etc
when I try to use those fileds for calculation, for example 1.99 + 0.99, it
will show 2.97 not 2.98 (just for example). Then I checked for the field,
the number I keyed in 0.99 will become 0.988888888888889, so I might get
problem for calculation. Does anyone know what the problem is?


Regards,

Daryl

 

[Guest]

start with double precision, not single

 

[Allen Browne]

Floating point numbers such as Single and Double are imprecise, as you find
when you perform further math on them, or you discover that 2 numbers that
look the same don't actually match.

Using a Double will give you more digits of precision, but ultimately the
issue is still there. This is true in all computer languages.

If you need no more than 4 places after the decimal point, you can solve the
problem by using the Currency type.

There is also a Decimal type in Access 2000 and later which is scalable to
more than 4 places, but it is too buggy to use:
http://allenbrowne.com/bug-08.html

 

[John Vinson]

Dear All,

I have a lots of trouble of rounding the number (single). Can somebody help.
I have declared a "Single" field and input some numbers for example 12.23,
1.99, 0.99 ...etc
when I try to use those fileds for calculation, for example 1.99 + 0.99, it
will show 2.97 not 2.98 (just for example). Then I checked for the field,
the number I keyed in 0.99 will become 0.988888888888889, so I might get
problem for calculation. Does anyone know what the problem is?


Regards,

Daryl

Single numbers are *approximations* - as are Double. They're stored as
a binary fraction; just as the number 1/7 cannot be accurately
represented as a finite decimal expansion (0.142856142856142856...),
0.99 cannot be represented exactly as a finite binary fraction. I bet
it's actually Double and 0.98999999999999 or so - Doubles are accurate
to about 14 decimal places, while Singles are accurate to about 7.

I'd suggest that if you need no more than four decimal places, that
you not use *any* kind of Number datatype. Instead use the Currency
datatype. It's a scaled huge integer with exactly four decimals, and
*no* roundoff error.

John W. Vinson[MVP]

 

[Jamie Collins]

There is also a Decimal type in Access 2000 and later which is scalable to
more than 4 places, but it is too buggy to use:
http://allenbrowne.com/bug-08.html

I'm still happily using the Decimal data type, bug free.

The bug Allen describes at the linked page relates to the cursor engine
i.e. is a problem with the technology used to sort data, rather than
the data type itself.

If the data type was flawed then we'd all be in trouble because
Access/Jet uses the Decmial data type under the covers e.g. try

SELECT TypeName(0.5)

and see what you get.

The biggest setback is not being able to directly cast a value as
decimal but it isn't a show-stopper. Jet 4 includes a CDBL() cast
function but it appears to be broken.

Jamie.

--