Phantom decimal places

[Guest]

I have field defined as number/single. When performing simple DLookups an/or
simple calculations on them and then displaying the result in text boxes, it
is often displayed with extra digits. For example, an entry that was
calculated as 30 - 30.22 shows -1.7800007.

The text box itself has decimal places set to Auto (which is correct,
because it could vary) and no format, although General Number format produces
the same result. The result is also the same whether or not the format in the
table is blank or General Number.

How do I get numbers to display the correct data without the ghost decimal
places?

 

[Allen Browne]

These inaccuracies are inherent in floating point numbers.

Some numbers (e.g. 1/3) cannot be represented accurately as a decimal, as
they require an infinite number of decimal places. Your computer can provide
around 7 or 8 accurate places (for a Number of size Single) or double that
(for a Number of size Double).

If you do not need more than 4 decimal places, you can use a Currency type
instead. Currency is a fixed-point type, not a floating-point type, and so
it avoids the rounding problems.

If you need more places, consider using a Double rather than a single, and
rounding the results of your calculations using the Round() function so the
small errors are not aggregated.

 

[John Vinson]

I have field defined as number/single. When performing simple DLookups an/or
simple calculations on them and then displaying the result in text boxes, it
is often displayed with extra digits. For example, an entry that was
calculated as 30 - 30.22 shows -1.7800007.

The text box itself has decimal places set to Auto (which is correct,
because it could vary) and no format, although General Number format produces
the same result. The result is also the same whether or not the format in the
table is blank or General Number.

How do I get numbers to display the correct data without the ghost decimal
places?

Single (and Double as well) are APPROXIMATIONS. They're stored as a
binary fraction and an exponent.

Just as the fraction 1/7 cannot be represented exactly as a decimal
number - it's an infinite repeat 0.142856142856142856... - so 1.78
cannot be expressed as an exact binary fraction. You'll get roundoff
error.

If you can get by with exactly four decimal places, no more, no fewer,
you can use a Currency datatype; it's a huge scaled integer without
this roundoff error problem. A2002 and later have a Decimal datatype,
but I've seen multiple posts warning that Access doesn't always handle
this datatype correctly (indexing and sorting seem to be problems
sometimes).

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps