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
LVL 1
crystal_TechAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
mbizupConnect With a Mentor Commented:
Most likely becuse Text1(0) is null.  Try this:

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


Ot if Text1 is a textbox, do you simply mean this?

                 Set CMLrRs = Nothing
                 If CMLrRs.State = 1 Then CMLrRs.Close
                 CMLrRs.Open "select * from B_Detail where BNo = " & NZ(Text1,0) & "", con, adOpenKeyset, adLockOptimistic
0
 
rajvjaConnect With a Mentor Commented:
CMLrRs.Open "select * from B_Detail where BNo = " & CINT(Text1(0)) & "", con, adOpenKeyset
0
 
rajvjaCommented:
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
0
 
Dale FyeCommented:
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.