SQL VBA syntax issue

Question

This select statement works.

rs.Open "Select Company from Customers where Company LIKE '" & Replace(Range("K4").Value, "'", "''") & "%' "

This select statement doesn't work.

rs.Open "Select Company from Customers where '" & Range("N4").Value & "' LIKE '" & Replace(Range("K4").Value, "'", "''") & "%' "

I'm going to have a data validation drop down box where the user can pick between a few options so being able to change the second Company in the first select statement to the users selection would be handy. Something is wrong with the syntax because although it doesn't give me any errors it doesn't have any results.

Answer 1

You shouldn't quote the column name - notice there are no quotes in your first example.

rs.Open "Select Company from Customers where " & Range("N4").Value & _
         " LIKE '" & Replace(Range("K4").Value, "'", "''") & "%' "