CODEWITHSUNDEEP
sqlvbams-access
Bawn
Bawn

Reputation: 509

Error with my SQL in vba

Dim rs As DAO.Recordset

sSQL = "SELECT Stock, Scanned FROM Products&
WHERE ((([Stock])=Forms!Products![StockCode]))                                                
And ((([Scanned])=Forms!Products![Scanned Information]));"

Set rs = CurrentDb.OpenRecordset(sSQL)

If rs.RecordCount = 0 Then
MsgBox "This is not correct"
Else
MsgBox "wooooo this works"
End If

I know my SQL query is wrong but I'm not sure how to structure it correctly and I keep getting errors. Am I suppose to break it up with more inverted commas and leave the Forms!Products![StockCode] outside the commas ?

Upvotes: 1

Views: 43

Answers (1)

GarethD
GarethD

Reputation: 69769

Your thought that you need to add it in with inverted commas is correct. This should work for you:

sSQL = "SELECT Stock, Scanned FROM Products " & _
        "WHERE  [Stock] = '" & Forms!Products![StockCode] &"' " & _                                              
        "AND    [Scanned] = '" & Forms!Products![Scanned Information] & "';"

Set rs = CurrentDb.OpenRecordset(sSQL)

Note I have removed all your parentheses, they are redundant, ((1)=(1)) means exactly the same as 1=1

Upvotes: 2

Related Questions