I hope this is an easy question for someone. I am using ADO to access some data. I need to write a SQL statement to narrow that data down. I can use the LIKE statement successfully for one option, but not multiple. Here is some code.
'This string will work
strSQL = "Field1 LIKE '%" & txtOption1 & "%'"
'I need something like this to work
strSQL = "Field1 LIKE '%" & txtOption1 & "&' AND Field2 LIKE '%" & txtOption2 & "%' AND Field3 LIKE '%" & txtOption3 & "%'"
Set rsRecordSet = New ADODB.Recordset
.CursorType = adOpenStatic
.CursorLocation = adUseClient
.LockType = adLockPessimistic
.Source = "SELECT * FROM Table1 WHERE " & strSQL
.ActiveConnection = connConnection
I get the follwoing error when I run it with the SQL statement that has multiple LIKE statements in it. ( I put "test" in txtOption2)
** Syntex error (missing operator) in query expression 'AND Field2 LIKE '%test%" **
Thanks for the help!