Problem: Querying Float Fields with a decimal

[FireFett]

Hello everyone

I have a table that contains a FLOAT(13,1) field

When I perform a query on it if the select statement on that field tries to find a value of 5.1 it will not return a record.

Example of Data in Table
Rec1 5.0
Rec2 5.1

If I Perform the following SQL Statement

SELECT AcctNum
FROM sl_member
WHERE (AcctNum = 5.1)

No Records Returned but should have returned rec 2

However if I do the following
SELECT AcctNum
FROM sl_member
WHERE (AcctNum = 5)

Or

SELECT AcctNum
FROM sl_member
WHERE (AcctNum = 5.0)

Rec 1 is returned

Anyone have a solution for me being able to select on a float field?

 

[sleipnir214]

Yeah. Don't do it. IEEE binary representation of floating-point numbers is inexact.

See

http://dev.mysql.com/doc/refman/5.0/en/problems-with-float.html

Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[FireFett]

Thanks I had just found that out when I got your reply.

I was basing my data types on the Visual basic/MySQL data Types Document that says Singles in VB = Floats in MySQL I have since made changes to the table to convert the floats to doubles.

 

[sleipnir214]

I'm not sure that columns of type DOUBLE are necessarily going to fix your problem.

In your specific example, you were querying for the value 5.1. 0.1 is, in IEEE binary floating-point format, the equivalent of an decimal infinitally-repeating decimal.

I recommend you look at DECIMAL columns or, since you're only using one decimal place, storing all your numbers as integers and shifting the decimal as needed when you display values or accept input from a user to do a search.

Want the best answers? Ask the best questions!

TANSTAAFL!!