asked on

Syntax Error (Missing Operator) in query expression '

Syntax Error (Missing Operator) in query expression 'BNo = '

in B_Detail Table
BNo ( Data Type is Number and Field size is Double

                 Set CMLrRs = Nothing
                 If CMLrRs.State = 1 Then CMLrRs.Close
                 CMLrRs.Open "select * from B_Detail where BNo = " & Text1(0) & "", con, adOpenKeyset, adLockOptimistic

Open in new window

Why i am getting this error

SOLUTION

rajvja

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

rajvja

Try storing the query in a variable and display how it is forming the query

Or Text(o) might be returning as text. Convert it to INT

ASKER CERTIFIED SOLUTION

mbizup

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Dale Fye

I would agree with mbizup, that Text1(0) is NULL.

Using the NZ() function to convert that NULL value to a numeric should resolve that issue.

However, you may find that you don't get the results that you are looking for using this method. Double precision numbers are frequently off in the way they are stored in the database because of the limits of representing decimal places as binary values. So when you query for BNo = 1.3456, what may actually be stored in the database is 1.3455999, and since those two values are not precisely the same, the record would not be returned by your query.

If your BNo values will all be integers, I would strongly recommend that you change the datatype from double to integer or Long Int data type.