Small Math Errors when Converting Text to Single

[TC]

I am getting small math errors when I use a query to convert a column's
data type from Text to Single. I am using a calculated column with the
following expression.

HighSchoolGPA: IIf(IsNull([GPA]),Null,CSng([GPA]))

When GPA = "3.10", HighSchoolGPA := 3.0999999046325 7, not 3.1. For this
particular application, the difference between 3.0999999046325 7 and 3.1
is unacceptable.

A couple of notes: When I evaluate
IIf(IsNull("3.1 0"),Null,CSng(" 3.10")) in the immediate window, I get
the correct result of 3.1. Also, the table containing GPA is linked via
ODBC to a SQL Server database.

I can easily workaround the problem by using the Round() function, but
I'd prefer to understand why I'm getting the problem in the first
place. Can anyone offer advice?


-TC

[Lyle Fairfield]

Re: Small Math Errors when Converting Text to Single

TC wrote:

I am getting small math errors when I use a query to convert a column's
data type from Text to Single.

http://msdn2.microsoft.com/en-us/lib...em.single.aspx

the page includes:

"Remember that a floating-point number can only approximate a decimal
number, and that the precision of a floating-point number determines
how accurately that number approximates a decimal number. By default, a
Single value contains only 7 decimal digits of precision, although a
maximum of 9 digits is maintained internally. The precision of a
floating-point number has several consequences:

Two floating-point numbers that appear equal for a particular precision
might not compare equal because their least significant digits are
different.

A mathematical or comparison operation that uses a floating-point
number might not yield the same result if a decimal number is used
because the floating-point number might not exactly approximate the
decimal number.

A value might not roundtrip if a floating-point number is involved. A
value is said to roundtrip if an operation converts an original
floating-point number to another form, an inverse operation transforms
the converted form back to a floating-point number, and the final
floating-point number is equal to the original floating-point number.
The roundtrip might fail because one or more least significant digits
are lost or changed in a conversion."