Improve company productivity with a Business Account.Sign Up

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 236
  • Last Modified:


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!
  • 3
  • 2
  • 2
  • +2
1 Solution
Ryan ChongCommented:

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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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?
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!!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 3
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now