Syntax run-time error

I get error Syntax error (missing operator) in query expression 'Name LIKE '%V%@'' on this code

Code:

---

        Select Case cmbOperation(0).ListIndex

        Case 0

            strFilter = strFilter & " LIKE '%" & txtFilter(0).Text & "%'"

        Case 1

            strFilter = strFilter & " = '" & txtFilter(0).Text & "'"

        Case 2

            strFilter = strFilter & " <> '" & txtFilter(0).Text & "'"

        Case 3

            strFilter = strFilter & " > '" & txtFilter(0).Text & "'"

        Case 4

            strFilter = strFilter & " >= '" & txtFilter(0).Text & "'"

        Case 5

            strFilter = strFilter & " < '" & txtFilter(0).Text & "'"

        Case 6

            strFilter = strFilter & " <= '" & txtFilter(0).Text & "'"

        End Select

 

    ConnectToDB

   

    Set mobjRst = New ADODB.Recordset

   

    'mobjRst.Open "SELECT * from Customer where " & Me.cmbFields.Text & strFilter

    strSQL = "SELECT * from Customer where " & Me.cmbFields.Text & strFilter

   

    mobjCmd.CommandText = strSQL

    Set mobjRst = mobjCmd.Execute

---

Debug line

Code:

---

Set mobjRst = mobjCmd.Execute

---

can anyone help?

Instead of this:

Code:

---

    mobjCmd.CommandText = strSQL

    Set mobjRst = mobjCmd.Execute

---

Try this:

Code:

---

    Set mobjRst = mobjCmd.Execute(strSQL)

---

Error Command text was not set for the command on that line

before you use the strSQL ... print it to the debug window or to a message box and make sure that the resulting SQL is in fact valid... I have a sneaky suspicion that you aren't getting the SQL you think you are. Also, what kind of database are you running against?

-tg

And you don't Execute a SELECT query with the command object, you use the recordset object to OPEN it.

Msgbox strFilter results in

LIKE '%V%'

when I remove the command object and use

Code:

---

mobjRst.Open "SELECT * from Customer where " & Me.cmbFields.Text & strFilter

---

I get error "The connection cannot be used to perform this operation. It's either closed or invalid in this context."

sorry forgot to mention i'm running it on an MS Access database

corrected the open line by adding the open connection to the end.

Now i'm getting the syntax error again.

Syntax error (missing operator) in query expression 'Name LIKE '%V%' on this code

Doesn't Access use * vs % for wild cards? I forget.

Anyway, Name is one of those fields that are kinda reserved, correct? Try using [Name] in your query instead. If that still generates an error, please post the entire sql statement so that we may have a look at it.

testing on another field looks like your correct LaVolpe it doesn't like Name, i'll do some more tests to make sure it's working ok now

does anyone know how I can use the word Name in a search without getting the syntax error?

The best thing to do is rename the field to something else - because even tho there is a fix in this situation, there are situations where you cannot fix it at all.

I would recommend changing it to something like CustomerName

For information on other unsafe field/table names, see the article What names should I NOT use for tables/fields/views/stored procedures/...? from our Database Development FAQs/Tutorials (at the top of the Database Development forum)