Type one number into Access, get another in the database

[Maury Markowitz]

Please READ CAREFULLY before saying this is an FAQ issue about storing
floating point numbers. It's not.

I have a form in Access linked to a table called tblPrices, which has
an ID, a date, and a price. When I type a date and the price "1.2"
into the form in Access and then look at the DB, it says the price is
"1.1999999999999996".

Wait, you're going to say this is because it can store floating point
numbers, aren't you?

Now I use a command line and do "UPDATE tblPrices SET price=1.2 WHERE
ID=12243". Now I go back to the database view and sure enough, the
price says "1.2".

If this was the inability to store floating points numbers, the second
attempt would fail too, right? So what's going on here.

Maury

 

[vanderghast]

Not, the second attempt won't fail to ***DISPLAY*** the value as 1.2.


Displaying is a tranformation made on the real stored data (binary IEEE)
and the process knows to make extra zero to dissapear (1.2, not 1.20000000)
and also to 'truncate' after some digits, so 1.199999999999999999 or
1.20000000000000001 wíll both be DISPLAYED as 1.2.

If you want to STORE exactly 1.2, use a DECIMAL data type, (or a CURRENCY
data type, but then, your precision is limited to 4 decimal digits), at
table design. DECIMAL is know as EXACT, while floating point (IEEE) are
known as INNEXACT.

Otherwise, always add a TOLERANCE when a comparison involves a FLOAT:

ABS( myFloat - 1.2) <= 1.0E-6

as example (which is what DISPLAYING does, at some point)




Vanderghast, Access MVP

 

[Piet Linden]

Maury,

How are you storing the price? As currency, or as a floating point
number (single or double)?

 

[Maury Markowitz]

Not, the second attempt won't fail to ***DISPLAY*** the value as 1.2.

I'm not sure I understand the tense here.

If I type "1.2" into a form field in Access, I see "1.199999..." in
the datasheet view.
If I type "1.2" into the datasheet form in Access, I see "1.2" in the
datasheet view, and form fields.
If I look in the database using an external editor, in this case Query
Analyzer, the first returns "1.19999..." while the second returns
"1.2".

So that implies that the transformation is occurring in the form
fields when being saved out, not at display time. Is there something
wrong with that logic?

Maury

 

[vanderghast]

1.2 is exact in base 10 because the infinite sequence is filled with zeros
and any eventual truncation of infinite sequence of decimal zeros don't lose
anything.

But computers don't work in base 10, but in base 2. So, when the value is
saved in memory, it is converted to base 2 representation, and there, the
infinite sequence is NOT strictly zero, and truncation, due to the finite
number of bits used to store the number, creates an error. In fact, it is
not pure truncation, but IEEE will have the closest possible value, in base
2. On the other hand, when you read back that value and FORMAT it, the
programs which is used to format the floating point number may be aware of
closest match, and display 1.2 rather than 1.19999998 as example.

If you chose a decimal representation, rather than a floating point
representation, then you can have an EXACT match between the base 10
representation and what is stored in the memory of the computer.
Historically, decimal numbers representation were much slower than binary
floating point precision, so, we have much more binary floating point values
around than decimal representation values. The decimal representation simply
stores the value as an integer, here, 1.2 12, and a scale, here -1. Since it
only involves integers, the conversion base 10 - base 2 is always exact, if
you are within the announced limit precision (number of base 10 digits).



Vanderghast, Access MVP

Not, the second attempt won't fail to ***DISPLAY*** the value as 1.2.

I'm not sure I understand the tense here.

If I type "1.2" into a form field in Access, I see "1.199999..." in
the datasheet view.
If I type "1.2" into the datasheet form in Access, I see "1.2" in the
datasheet view, and form fields.
If I look in the database using an external editor, in this case Query
Analyzer, the first returns "1.19999..." while the second returns
"1.2".

So that implies that the transformation is occurring in the form
fields when being saved out, not at display time. Is there something
wrong with that logic?

Maury