Precision

[Martin]

I hope this is the appropriate group for this. If not, please let me know.

I have noticed that data entered in a form is not being stored the way it
was entered. For example, 0.20 seems to be stored as 0.1999999....... or
something like that. I discovered this because a query I was writing would
find the incorrect number of records when qualifying on, say, 0.20.

I have tried the format property on the table field, but that appears to
simply format the display/report, not the value stored in the table. I am
confused.

Has anyone seen this before and how did they get the value to store correctly?

Thanks.

 

[Tom Ellison]

Dear Martin:

It is a common mistake to store data in floating point datatypes (single or
double) when it is not scientific or engineering measurements. These
datatypes are probably inappropriate for your use. Change to another
datatype for these columns. Even if the value being stored does not
represent actual money, the money or currency datatypes are good for storing
your information without the "estimation" you have encountered.

Tom Ellison

 

[Martin]

Will these currency or money datatypes allow as much as 6 decimal places? If
not, I will need to stay with the single datatype to get that precision.
(This is for a different project, not the one I had in my original post.)

Thanks.

 

[Tom Ellison]

Dear Martin:

Access Jet does not have a datatype giving more than 4 places. You should
use Access MSDE as the database, where the "Decimal" datatype is more than
sufficient for your needs.

Tom Ellison

 

[Martin]

Thanks for your insight Tom. I guess I still have a problem with Access
storing a value entered as 0.20 as something else, regardless of the decimal
precision available on the various "decimal" datatypes (single, double,
currency, etc). I guess it is another thing to deal with.

Thanks again.

 

[Tom Ellison]

Dear Martin:

The real datatypes store values in binary. To the right of the decimal
point, the binary digits have values of 1/2, 1/4, 1/8, and so on. It is not
possible to store decimal data this way with perfect accuracy. 1/10 can be
approximated by a series of binary digits, but never exactly.

Binary is the "native" mode for computers, but they can do more, but less
efficiently. Access will store values in a wide range of modes, and it is
up to the designer to choose them reasonably. You could store your numeric
data as strings, for that matter. Not necessarily reasonable, but possible.
You have chosen a less reasonable, but possible approach here, and you're
getting bit.

Tom Ellison

 

[mnature]

Don't know if you have tried this. Data Type Number, Field Size Decimal,
Precision 18, Scale 6, Decimal Places 6. There is a discussion of data types
as used in Visual Basic:

http://msdn2.microsoft.com/en-us/library/ae382yt8.aspx

 

[Tom Ellison]

This is possible and may work. I do not steer others toward using Jet
Decimal datatype, because it is very buggy. For example, it does not index
properly. This may or may not be an issue for you. Also, it eats up a
large amount of space, if that is a concern.

But, otherwise, it might be a good choice.

Tom Ellison

 

[TC]

I guess I still have a problem with Access
storing a value entered as 0.20 as something else, regardless of the decimal
precision available on the various "decimal" datatypes

Martin, that is common to many computer systems. It is not unique to
Access, at all.

Most computers use a so-called "floating point" representation to store
non-integer numbers. However, a floating point scheme can never
identify every possible value between the low & high limits. There are
some numbers (within those limits) that simply can't be represented.
This is a common computing issue, it is not unique to Access.

Certain applications (eg. cryptography) require exact calculations on
very large and/or fractional numbers. Those applications will never use
the normal floating point numbers on the PC. They use "bignum
libraries", which are, libraries of code that is written specifically
to handle these problems.

Normal PC software does not use bignum libraries generally.

HTH,
TC (MVP Access)
http://tc2.atspace.com

 

[Tom Ellison]

Dear Martin:

What TC says is just the point.

Actually, decimal representation has the same difficulty. What is 1/3 in
decimal notation? It can never be represented exactly, no matter how many
places you provide. It is possible to provide calculation facilities that
can represent 1/3, exactly, but then 1/4 probably couldn't be represented
exactly.

If the values you want to store are naturally represented as decimals and
must be stored exactly, then use a decimally based numeric datatype. Float,
real, single, and double datatypes are NOT decimally based. They are binary
based. This provides some storage and calculation efficiency, but cannot
represent all decimal values exactly.

The choice is yours. Make a good choice and you won't have problems.

Tom Ellison