VBA Query to

[Mr_Si]

VBA Query gets Syntax Error (missing operator)

Hi there,

I'm getting an error with the following message which I wonder if anyone could help me with? :

Run-time Error '3075'
Syntax Error (Missing Operator) in query expression
'tblContact.chrContactCompany LIKE A bit of Text'

Basically, I have a form:

I enter the text in, select the area of the db to search, and it runs the relevant query with the typed in text ("A bit of Text" in this case) within the where clause. Then it opens the form which uses the query as its source.


here's my code:

Option Compare Database
Option Explicit

Private Sub btnSearch_Click()

    Dim BeginningSQL As String
    Dim SearchContactWhere As String
    Dim ContactSearchFinal As String
    
    ' If the textbox has no value then tell user they need to enter something
    'otherwise, move to the case statement in the else clause
    
    If IsNull(Me.TxtSearchText) Then
        MsgBox "No text entered. Please enter some text to search"
        Exit Sub
        
    Else
        'MsgBox Me.TxtSearchText, vbInformation, "This is in the string"
        
        Select Case (cboSearchPlace)
     
            Case Is = "Contacts"
                
                BeginningSQL = "SELECT tblContact.chrContactCompany, tblContact.chrContactFirstName, tblContact.chrContactSurname, tblContact.idsContact_ID FROM tblContact WHERE "
                
                SearchContactWhere = "tblContact.chrContactCompany" & " LIKE " & Me.TxtSearchText ' "Or tblContact.chrContactFirstName LIKE " & Forms!frmSearch!TxtSearchText "Or tblContact.chrContactSurname LIKE " & Forms!frmSearch!TxtSearchText
                    MsgBox SearchContactWhere ' messagebox for error checking
                    
                ' concatenate the 2 bits of SQL to make the full statement
                ContactSearchFinal = BeginningSQL & SearchContactWhere
                    MsgBox ContactSearchFinal
                
                'replace the query "qrySearchContacts" with the VBA created string
                CurrentDb.QueryDefs("qrySearchContacts").SQL = ContactSearchFinal
                    MsgBox ContactSearchFinal
                
                'open the form which is based on the query "qrySearchContacts"
                DoCmd.OpenForm "frmSearchContactsResults"
         
            Case Is = "Sites"
                MsgBox "Searching in Sites only", vbInformation
                Exit Sub
            
            Case Is = "Products"
                MsgBox "Searching in Products only", vbInformation
                Exit Sub
                
            Case Else
                MsgBox "Please enter a section to search", vbOKOnly
                Exit Sub
                
        End Select
        
    End If
    
                
End Sub

I'm getting quite desperate now! I shall keep trying though.

 

[namliam]

You have not quoted your text

YourWhereString = " YourField like '" & me.YourTextField & "'"

Good luck

 

[georgedwilkinson]

Try:

Private Sub btnSearch_Click()
 
    Dim BeginningSQL As String
    Dim SearchContactWhere As String
    Dim ContactSearchFinal As String
 
    ' If the textbox has no value then tell user they need to enter something
    'otherwise, move to the case statement in the else clause
 
    If IsNull(Me.TxtSearchText) Then
        MsgBox "No text entered. Please enter some text to search"
        Exit Sub
 
    Else
        'MsgBox Me.TxtSearchText, vbInformation, "This is in the string"
 
        Select Case (cboSearchPlace)
 
            Case Is = "Contacts"
 
                BeginningSQL = "SELECT tblContact.chrContactCompany, tblContact.chrContactFirstName, tblContact.chrContactSurname, tblContact.idsContact_ID FROM tblContact WHERE "
 
                SearchContactWhere = "tblContact.chrContactCompany" & " LIKE [B][COLOR=red]'*[/COLOR][/B]" & Me.TxtSearchText [COLOR=red][B]& "*';[/B][/COLOR]" 'Not sure what your intent was after that -- "Or tblContact.chrContactFirstName LIKE " & Forms!frmSearch!TxtSearchText "Or tblContact.chrContactSurname LIKE " & Forms!frmSearch!TxtSearchText
                    [COLOR=red][B]Debug.Print[/B][/COLOR] SearchContactWhere ' Print makes troubleshooting easier to share on the forum.
 
                ' concatenate the 2 bits of SQL to make the full statement
                ContactSearchFinal = BeginningSQL & SearchContactWhere
                    [COLOR=red][B]Dubug.Print[/B][/COLOR] ContactSearchFinal
 
                'replace the query "qrySearchContacts" with the VBA created string
                CurrentDb.QueryDefs("qrySearchContacts").SQL = ContactSearchFinal
                    MsgBox ContactSearchFinal
 
                'open the form which is based on the query "qrySearchContacts"
                DoCmd.OpenForm "frmSearchContactsResults"
 
            Case Is = "Sites"
                MsgBox "Searching in Sites only", vbInformation
                Exit Sub
 
            Case Is = "Products"
                MsgBox "Searching in Products only", vbInformation
                Exit Sub
 
            Case Else
                MsgBox "Please enter a section to search", vbOKOnly
                Exit Sub
 
        End Select
 
    End If
 
 
End Sub

If you're still having problems, copy the immediate window and paste it here along with the symptom.

 

[Mr_Si]

Thanks guys!

That worked fine! I knew it must have had something to do with the quote marks and parenthesis etc, but couldn't work it out.

With regard to the OR bits further on, mailman, I was wanting use OR statements in my where clause so that if the text entered into the textbox existed in either Companyname, firstname or Surname then it'd return the results based on searching in all three fields.

I shall play.

Best Regards,

Simon


P.S. Sorry the title went a bit weird, I shall see if I can edit it.

 

[georgedwilkinson]

Glad you got it working.

 

[Mr_Si]

I wonder if there's a way to change the WHERE clause:

SearchContactWhere = "tblContact.chrContactCompany" & " LIKE '*" & Me.TxtSearchText & "*'" & " Or " & "tblContact.chrContactFirstName" & " Like '*" & Me.TxtSearchText & "*'" & " Or " & "tblContact.chrContactSurname" & " Like '*" & Me.TxtSearchText & "*'"

to search for any field in the table, just like using "tblContact.*", I've tried, but either it's not allowable, or I get the various quote marks in the wrong places...?

Si

 

[georgedwilkinson]

Use the Debug.Print trick I mentioned along with cut and paste from the immediate window.

 

[Mr_Si]

I wonder if I'm doing this correctly, because debug.print doesn't seem to do anything, it just highlights a line of code telling me the following error:

Case Is = "Sites"
                
                MsgBox "Searching in Sites only", vbInformation
                
                BeginningSQL = "SELECT tblSite.* FROM tblSite WHERE "

                'SearchContactWhere = "tblSite.chrSiteAddress1" & " LIKE '*" & Me.TxtSearchText & "*'" & " OR " & "tblSite.chrSiteAddress2" & " LIKE '*" & Me.TxtSearchText & "*'" & " OR " & "tblSite.chrSiteAddress3" & " LIKE '*" & Me.TxtSearchText & "*'" & " OR " & "tblSite.chrSiteTown" & " LIKE '*" & Me.TxtSearchText & "*'" & " OR " & "tblSite.chrSiteCounty" & " LIKE '*" & Me.TxtSearchText & "*'" & " OR " & "tblSite.chrSitePostcode" & " LIKE '*" & Me.TxtSearchText & "*'"
                SearchContactWhere = "tblSite.*" & " LIKE '*" & Me.TxtSearchText & "*'"
                    Debug.Print SearchContactWhere ' messagebox for error checking
                    
                ' concatenate the 2 bits of SQL to make the full statement
                ContactSearchFinal = BeginningSQL & SearchContactWhere
                    MsgBox ContactSearchFinal
                
                'replace the query "qrySearchSite" with the VBA created string
                CurrentDb.QueryDefs("qrySearchSite").SQL = ContactSearchFinal ' <----------- ERROR HERE ACCORDING TO THE HIGHLIGHTED YELLOW BIT
                    Debug.Print ContactSearchFinal
                
                'open the form which is based on the query "qrySearchSite"
                DoCmd.OpenForm "frmSearchSiteResults"
                
                Exit Sub

 

[georgedwilkinson]

Can you upload your database.

How To Upload A Database To The Forum

 

[Mr_Si]

it's too big to upload took all imaging out and it's still over 3 meg when compacted

 

[georgedwilkinson]

Drag, then what line of code does it highlight?

 

[Mr_Si]

ok zipped it but it was 850KB which is still too large.

 

[Mr_Si]

 'ERROR HERE ACCORDING TO THE HIGHLIGHTED YELLOW BIT
CurrentDb.QueryDefs("qrySearchSite").SQL = ContactSearchFinal

 

[georgedwilkinson]

Then the error did not occur on the Debug.Print line. What does it say in the immediate window?

 

[Mr_Si]

Hi George,

This is what it does:

 

[Mr_Si]

maybe because some of the field types are not characters or memos and a Type Mismatch is occurring...?

 

[namliam]

As far as I know you cannot do something like:
tblSite.* LIKE '*find this*'

It is simply not possible... as far as I know (tho I havent tested it)
you have to do something like:
Field1 like '**'
or field2 like '**'
or ... etc

Offcourse you can make a loop to go thru all fields and add them to the where... but I doubt that would be usefull.

 

[Mr_Si]

Thanks namliam, that is the answer I needed.

 

[georgedwilkinson]

You know, that wasn't in your original post. It certainly would have made it a lot easier to help you fix that had you included what is really happening in your code.

FYI. You can view your immediate window in VBA with <ctrl>g.

 

[Mr_Si]

Oh Pants, I'm sorry. I thought I had.

But thank you for that <ctrl>+g suggestion. I shall remember that.