Missing Operator

[Dinger_80]

So I have created a select query and I am using it as an all purpose search query. I plan on changing the value of SQL in the query based upon what the user selects as the method of searching. So there is a drop down menu, that allows the user to select the method, this will be expanded in the future but for now till I get this working I am not going to bother with that. That being said, I figure once I get a couple of these the work the others should follow suit. Anyway, here is what I have so far.

Dim StrSQL As String
Dim qdf As DAO.QueryDef

Select Case Me.Combo3
    Case "Full Name"
        StrSQL = "SELECT TestRequestTable.TRNumber, TestRequestTable.DateSubmitted, CrouseHindsPersonalTable.FullName, " & _
                "TestingProcedureTable.SpecificNameOfTest, CatalogNumberTable.CatalogNumber, ComponentTable.ComponentPartNumber, " & _
                "IntervalsTable.IntervalsNumber " & _
                "FROM ((((TestRequestTable INNER JOIN CrouseHindsPersonalTable ON TestRequestTable.Requestor = CrouseHindsPersonalTable.ID) " & _
                "INNER JOIN TestingProcedureTable ON TestRequestTable.TypeOfTest = TestingProcedureTable.ID) INNER JOIN IntervalsTable ON " & _
                "TestRequestTable.IntervalsNumber = IntervalsTable.ID) INNER JOIN CatalogNumberTable ON TestRequestTable.CatalogNumber = CatalogNumberTable.ID) " & _
                "INNER JOIN ComponentTable ON TestRequestTable.ComponentTested = ComponentTable.ID " & _
                "WHERE (CrouseHindsPersonalTable.FullName) =  Like *" & Me.Text0 & "*"
                
    Case "Catalog Number"
        StrSQL = "SELECT TestRequestTable.TRNumber, TestRequestTable.DateSubmitted, CrouseHindsPersonalTable.FullName, " & _
                "TestingProcedureTable.SpecificNameOfTest, CatalogNumberTable.CatalogNumber, ComponentTable.ComponentPartNumber, " & _
                "IntervalsTable.IntervalsNumber " & _
                "FROM ((((TestRequestTable INNER JOIN CrouseHindsPersonalTable ON TestRequestTable.Requestor = CrouseHindsPersonalTable.ID) " & _
                "INNER JOIN TestingProcedureTable ON TestRequestTable.TypeOfTest = TestingProcedureTable.ID) INNER JOIN IntervalsTable ON " & _
                "TestRequestTable.IntervalsNumber = IntervalsTable.ID) INNER JOIN CatalogNumberTable ON TestRequestTable.CatalogNumber = CatalogNumberTable.ID) " & _
                "INNER JOIN ComponentTable ON TestRequestTable.ComponentTested = ComponentTable.ID " & _
                "WHERE (CatalogNumberTable.CatalogNumber) = Like *" & Me.Text0 & "*"
End Select
Debug.Print StrSQL
CurrentDb.QueryDefs("Query1").SQL = StrSQL
DoCmd.Close acQuery, "Query1"
DoCmd.OpenQuery "Query1"

The error occurs on the CurrentDb.QueryDefs(Query1").SQL = StrSQL. The full error reads "Error 3075: Syntax Error (missing operator) in query expression '(TableName.FieldName) = Like *ValueInTextBox*'."

Any help on this would be appreciated. Thank you.

 

[Galaxiom]

Drop the Equal sign. Like is the operator.

 

[Dinger_80]

Galaxiom,

I dropped the equal sign as suggested. Sadly I still have the same problem, just without an equal sign. I tried it on both just to make sure and neither of them work.

 

[Galaxiom]

You need single quotes as text delimiters around string values.

"WHERE (CatalogNumberTable.CatalogNumber) Like [B]'[/B]*" & Me.Text0 & "*[B]'[/B]"

Note that you will also run into problems if the value in the textbox includes an apostrophe. This can be overcome by doubling that character before the value is put into the SQL.

 

[jdraw]

Does the code run as far as the Debug.print? Can you post the value printed in the immediate window.
Have you tried putting the query sql into the query wizard. It may help you pinpoint the issue.
As Galaxiom said --remove the =, that is one issue for sure.

 

[Dinger_80]

Galaxiom,

The single quotes did the trick. Thank you for your help.

jdraw,

The code ran past the debug and displayed in the immediate window the value in the text box.


The final code that works looks like:

Dim StrSQL As String
Dim qdf As DAO.QueryDef

Select Case Me.Combo3
    Case "Full Name"
        StrSQL = "SELECT TestRequestTable.TRNumber, TestRequestTable.DateSubmitted, CrouseHindsPersonalTable.FullName, " & _
                "TestingProcedureTable.SpecificNameOfTest, CatalogNumberTable.CatalogNumber, ComponentTable.ComponentPartNumber, " & _
                "IntervalsTable.IntervalsNumber " & _
                "FROM ((((TestRequestTable INNER JOIN CrouseHindsPersonalTable ON TestRequestTable.Requestor = CrouseHindsPersonalTable.ID) " & _
                "INNER JOIN TestingProcedureTable ON TestRequestTable.TypeOfTest = TestingProcedureTable.ID) INNER JOIN IntervalsTable ON " & _
                "TestRequestTable.IntervalsNumber = IntervalsTable.ID) INNER JOIN CatalogNumberTable ON TestRequestTable.CatalogNumber = CatalogNumberTable.ID) " & _
                "INNER JOIN ComponentTable ON TestRequestTable.ComponentTested = ComponentTable.ID " & _
                "WHERE (CrouseHindsPersonalTable.FullName) Like '*" & Me.Text0 & "*'"
                
    Case "Catalog Number"
        StrSQL = "SELECT TestRequestTable.TRNumber, TestRequestTable.DateSubmitted, CrouseHindsPersonalTable.FullName, " & _
                "TestingProcedureTable.SpecificNameOfTest, CatalogNumberTable.CatalogNumber, ComponentTable.ComponentPartNumber, " & _
                "IntervalsTable.IntervalsNumber " & _
                "FROM ((((TestRequestTable INNER JOIN CrouseHindsPersonalTable ON TestRequestTable.Requestor = CrouseHindsPersonalTable.ID) " & _
                "INNER JOIN TestingProcedureTable ON TestRequestTable.TypeOfTest = TestingProcedureTable.ID) INNER JOIN IntervalsTable ON " & _
                "TestRequestTable.IntervalsNumber = IntervalsTable.ID) INNER JOIN CatalogNumberTable ON TestRequestTable.CatalogNumber = CatalogNumberTable.ID) " & _
                "INNER JOIN ComponentTable ON TestRequestTable.ComponentTested = ComponentTable.ID " & _
                "WHERE (CatalogNumberTable.CatalogNumber) Like '*" & Me.Text0 & "*'"
End Select

CurrentDb.QueryDefs("Query1").SQL = StrSQL
DoCmd.Close acQuery, "Query1"
DoCmd.OpenQuery "Query1"

 

[jdraw]

Good stuff. Glad you have it working, and thanks for posting the solution.