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
With rsRecordSet
    .CursorType = adOpenStatic
    .CursorLocation = adUseClient
    .LockType = adLockPessimistic
    .Source = "SELECT * FROM Table1 WHERE " & strSQL
    .ActiveConnection = connConnection
End With

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!
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:

strSQL = "Field1 LIKE '%" & replace(txtOption1,"'","''") & "' AND Field2 LIKE '%" & replace(txtOption2,"'","''") & "%' AND Field3 LIKE '%" & replace(txtOption3,"'","''") & "%'"


strSQL = "Field1 LIKE '%" & replace(txtOption1,"'","''") & "%' AND Field2 LIKE '%" & replace(txtOption2,"'","''") & "%' AND Field3 LIKE '%" & replace(txtOption3,"'","''") & "%'"

Shiju SasidharanAssoc Project ManagerCommented:
Try this
strSQL = "Field1 LIKE '%" & Replace(txtOption1,"'","''") & "%' AND Field2 LIKE '%" & Replace(txtOption2,"'","''") & "%' AND Field3 LIKE '%" & Replace(txtOption3,"'","''")  & "%'"

u put an extra   &   instead of   %


Shiju SasidharanAssoc Project ManagerCommented:

sorry i am late
ryancys  deserves it

The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

APlusComp247Author Commented:
I understand what you are getting at, but I haven't used any apostraphes in the textboxes so the Replace statement shouldn't change anything.... right?

I tried that and it didn't work.

Any more ideas??
What is in the value of strSQL when you execute the command?  Can you paste it in here so we can have a look?
Just add the line :
Debug.Print "SELECT * FROM Table1 WHERE " & strSQL
when you set the '.Source' to make it appear in the immediate window.

Which database are you using?

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
There is a very simple way to do this using the "Microsoft Script Control 1.0". All you need to do is create a string which is the expression you want to evaluate and just let the "Microsoft Script Control 1.0" evaluate it.

You can set the 'strExpression' string to any vb expression e.g.: "3 / 4" or "5 * (6 - 1)" etc.

Private Sub Command1_Click()
    Dim dblResult As Double
    Dim strExpression As String
    Dim MyScripControl As Object
    Set MyScripControl = CreateObject("ScriptControl")

    strExpression = "3 / 4"  '<---  You can put any expression you like here

    MyScripControl.Language = "VBScript"
    dblResult = MyScripControl.Eval(strExpression )
    MsgBox dblResult

End Sub
Please ignore the last comment, wrong question answered!
Try to put fields name into a brackets :
[Field1] Like ...
APlusComp247Author Commented:
Ok, I made a stupid mistake.  I was using a few IF  statements to create the SQL statement depending on whether data was entered into the textbox or not.  I used the following

strSQL = " AND Field1 LIKE '%" & txtOption1 & "%'"

I quickly figured out after using the debug.print than I needed the following

strSQL = strSQL & " AND Field1 LIKE '%" & txtOption1 & "%'"

I was so unsure of my SQL syntax that I completely forgot a little simple debuging.

Thanks for all the help!!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.