Syntax error in where clause in Access

Question

The error I am getting is Syntax error in where clause in Access.

Here is the code:

SQL = "Select * FROM tblPermitAgencyInformation  & WHERE [RecordID] = " & Me.AgencyInfoRecordID.Value
Set rs = db.OpenRecordset(SQL)

RecordID is an autonumber field and the AgencyInfoRecordID is an integer.

[How to debug dynamic SQL in VBA](http://stackoverflow.com/a/1099570/3820271) — Andre, Nov 14 '17 at 22:45

score 0 · Answer 1 · answered Nov 15 '17 at 00:11

0

It looks like you misread the article you state. It appears to be attempting to format the adhoc query in the text variable. Note: it says

strSQL = "SELECT wazzle FROM bamsploot" & vbCrLf & " WHERE plumsnooker = 0"

You need to make sure that you have the ampersands outside of the quotes, (they are used to append variables and strings together in this case)

Follow June7 advice and remove the ampersand there. It should help you get running.

Make your code this:

SQL = "Select * FROM tblPermitAgencyInformation WHERE [RecordID] = " & Me.AgencyInfoRecordID.Value

Hope that helps

answered Nov 15 '17 at 00:11

xDJR1875

It absolutely does. Thank you - I must have stared at it too long just dropped the & and it was fine. – JimP Nov 15 '17 at 13:28
Please mark as the answer if it is indeed what you feel the answer is. – xDJR1875 Nov 16 '17 at 22:11

1 Answers1