Lost decimal precision for single and double number format-Access2

[Pat]

I ran an update query to find certain values in a table and replace them with:

-9999.999999 for my single format columns (6 decimal places)
-9999.99999999999999 for my double format columns (14 decimal places)

Access presented a message saying it had done so but when I look at the
table, the updated values are -10,000.

If I repeat the update query to replace -10,000 with the above values,
Access presents a message saying it will replace the same number of rows as
before with the above values, meaning that it really is -10,000 that it
updated to the first time.

Why won't it give me the decimal precision it should?

Thanks,
Patrick

 

[John Spencer]

What is the field type? Is it Number? IF so, what is the field size
(Integer, Long Integer, Single, Double, or Decimal)?

Single seems to be good to 3 decimal places After that it rounds based on
the 4th decimal position
Double seems to be good to 11 decimal places after that it rounds based on
the 12th decimal position

That is an observation based on a quick test, so it may not be totally
accurate.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[John W. Vinson]

I ran an update query to find certain values in a table and replace them with:

-9999.999999 for my single format columns (6 decimal places)
-9999.99999999999999 for my double format columns (14 decimal places)

Access presented a message saying it had done so but when I look at the
table, the updated values are -10,000.

If I repeat the update query to replace -10,000 with the above values,
Access presents a message saying it will replace the same number of rows as
before with the above values, meaning that it really is -10,000 that it
updated to the first time.

Why won't it give me the decimal precision it should?

Thanks,
Patrick

A Single float number has 24 binary bits precision - approximately seven
decimal places IN ALL. That is, 3333333000000.00000000 is an approximation,
stored with a value somewhere between 3333332000000 and 333334000000. It's not
six or seven decimals *after the decimal point*.

Similarly, a Double stores approximately fourteen significant digits - the
position of the decimal point is irrelevant, you only get those 14 places.

If you need more, use a Decimal number datatype.

John W. Vinson [MVP]

 

[Pat]

Thanks John for clarifying. That was exactly the problem.

Patrick

 

[Pat]

Thanks John for the quick test, it's very helpful. This illustrates what
John Vinson said in the other response I received, that single number format
holds 7 significant digits and double number format holds 15 significant
digits (rather than places to the right of the decimal).

Patrick