Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 640
  • Last Modified:

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
0
crystal_Tech
Asked:
crystal_Tech
  • 2
2 Solutions
 
rajvjaCommented:
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
 
mbizupCommented:
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
 
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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now