Syntax run-time error

[vb help]

I get error Syntax error (missing operator) in query expression 'Name LIKE '%V%@'' on this 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

Set mobjRst = mobjCmd.Execute

can anyone help?

[Lenggries]

Instead of this:

    mobjCmd.CommandText = strSQL

    Set mobjRst = mobjCmd.Execute

Try this:

    Set mobjRst = mobjCmd.Execute(strSQL)

[vb help]

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

[techgnome]

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

[Hack]

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

[vb help]

Msgbox strFilter results in

LIKE '%V%'

when I remove the command object and use

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."

[vb help]

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

[vb help]

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

[LaVolpe]

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.

[vb help]

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

[vb help]

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

[si_the_geek]

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)