Field in Expression Displays Decimal Error (Access 2002)

  • Thread starter Thread starter croy
  • Start date Start date
C

croy

When I include my simple sort field (Single) in a saved
query, it displays data exactly as I would like and expect:

1
1.1
2
etc.

But when I put this expression in the query:

LocSort8:
IIf([LocSort]=8.1,8,IIf([LocSort]=8.2,8,IIf([LocSort]=8.4,8,[LocSort])))

.... then it displays results like:

1
1.10000002384186
2

Why would this be?
 
When I include my simple sort field (Single) in a saved
query, it displays data exactly as I would like and expect:

1
1.1
2
etc.

But when I put this expression in the query:

LocSort8:
IIf([LocSort]=8.1,8,IIf([LocSort]=8.2,8,IIf([LocSort]=8.4,8,[LocSort])))

... then it displays results like:

1
1.10000002384186
2

Why would this be?
Click to expand...

Because it's being converted from Single (an approximation with about 7
decimal places) to Double (a finer approximation with about 14 decimal
places).

It's worse than you think: depending on how LocSort is calculated, it may not
match the values in the IIF comparison! Floating point numbers are stored as a
binary fraction, and - just as 1/7 cannot be depicted exactly as a decimal
fraction - numbers like 8.1 cannot be depicted exactly in a binary fraction.
There will be "roundoff error" just as you have seen.

I'd suggest using a Decimal datatype and specifying your own scale and
precision to meet your needs.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com
 
When I include my simple sort field (Single) in a saved
query, it displays data exactly as I would like and expect:

1
1.1
2
etc.

But when I put this expression in the query:

LocSort8:
IIf([LocSort]=8.1,8,IIf([LocSort]=8.2,8,IIf([LocSort]=8.4,8,[LocSort])))

... then it displays results like:

1
1.10000002384186
2

Why would this be?
Click to expand...

Because it's being converted from Single (an approximation with about 7
decimal places) to Double (a finer approximation with about 14 decimal
places).

It's worse than you think: depending on how LocSort is calculated, it may not
match the values in the IIF comparison! Floating point numbers are stored as a
binary fraction, and - just as 1/7 cannot be depicted exactly as a decimal
fraction - numbers like 8.1 cannot be depicted exactly in a binary fraction.
There will be "roundoff error" just as you have seen.

I'd suggest using a Decimal datatype and specifying your own scale and
precision to meet your needs.
Click to expand...

Thanks for that--I think. ;-)

What is meant by scale?
 
When I include my simple sort field (Single) in a saved
query, it displays data exactly as I would like and expect:

1
1.1
2
etc.

But when I put this expression in the query:

LocSort8:
IIf([LocSort]=8.1,8,IIf([LocSort]=8.2,8,IIf([LocSort]=8.4,8,[LocSort])))

... then it displays results like:

1
1.10000002384186
2

Why would this be?
Click to expand...

Because it's being converted from Single (an approximation with about 7
decimal places) to Double (a finer approximation with about 14 decimal
places).

It's worse than you think: depending on how LocSort is calculated, it may not
match the values in the IIF comparison! Floating point numbers are stored as a
binary fraction, and - just as 1/7 cannot be depicted exactly as a decimal
fraction - numbers like 8.1 cannot be depicted exactly in a binary fraction.
There will be "roundoff error" just as you have seen.

I'd suggest using a Decimal datatype and specifying your own scale and
precision to meet your needs.
Click to expand...

Interesting. In all my occasional MS Access work over the
years, I had never used that (Decimal) datatype. I tried it
in this case, and it seemed to be the perfect fix. I used a
"precision" of 3, and a "scale" of 1, and it seems perfect
for this "sorting" field.

Thank you for your detailed explanation!
 
Back
Top