Field in Expression Displays Decimal Error (Access 2002)

[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?

 

[John W. Vinson]

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?

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

 

[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?

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.

Thanks for that--I think. ;-)

What is meant by scale?

 

[croy]

What is meant by scale?

I rescinded that post, but in case the rescind didn't work,
"Scale" just jumped out at me from behind some shrubbery in
my brain...

--

 

[John W. Vinson]

I rescinded that post, but in case the rescind didn't work,
"Scale" just jumped out at me from behind some shrubbery in
my brain...

<chuckle>

Been there... done that... too many times to recount!

--

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

 

[David W. Fenton]

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

Decimal data type is extremely buggy, no?

 

[John W. Vinson]

Decimal data type is extremely buggy, no?

Not in my experience. It was in the initial release of A2000 but that's been a
while.
--

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

 

[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?

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.

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!

 

[David W. Fenton]

Not in my experience. It was in the initial release of A2000 but
that's been a while.

http://allenbrowne.com/bug-08.html

It doesn't appear to me to be ready for prime time even 10 years
after it was introduced.