Error with my SQL in vba

Question

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 ?

GarethD · Accepted Answer · 2013-06-24 08:37:20Z

2

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

answered Jun 24, 2013 at 8:37

GarethD

70k11 gold badges89 silver badges130 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Error with my SQL in vba

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related