Posted on 2004-10-31
Last Modified: 2008-02-26
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!
Question by:APlusComp247
    LVL 48

    Expert Comment

    by:Ryan Chong

    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,"'","''") & "%'"

    LVL 14

    Expert Comment

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

    u put an extra   &   instead of   %


    LVL 14

    Expert Comment

    by:Shiju Sasidharan

    sorry i am late
    ryancys  deserves it


    Author Comment

    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??
    LVL 18

    Accepted Solution

    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?
    LVL 18

    Expert Comment

    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
    LVL 18

    Expert Comment

    Please ignore the last comment, wrong question answered!
    LVL 27

    Expert Comment

    Try to put fields name into a brackets :
    [Field1] Like ...

    Author Comment

    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!!

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Introduction This article makes the case for using two modules in your VBA/VB6 applications to provide both case-sensitive and case-insensitive text comparison operations.  Recently, I solved an EE question using the LIKE function.  In order for th…
    Introduction I needed to skip over some file processing within a For...Next loop in some old production code and wished that VB (classic) had a statement that would drop down to the end of the current iteration, bypassing the statements that were c…
    Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
    This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

    884 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now