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:

http://i6.photobucket.com/albums/y218/Mr_Si/searchform.jpg


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.

You have not quoted your text

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

Good luck

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 '*" & Me.TxtSearchText & "*';" 'Not sure what your intent was after that -- "Or tblContact.chrContactFirstName LIKE " & Forms!frmSearch!TxtSearchText "Or tblContact.chrContactSurname LIKE " & Forms!frmSearch!TxtSearchText
Debug.Print SearchContactWhere ' Print makes troubleshooting easier to share on the forum.

' concatenate the 2 bits of SQL to make the full statement
ContactSearchFinal = BeginningSQL & SearchContactWhere
Dubug.Print 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.

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.

Glad you got it working.

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

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

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:

http://i6.photobucket.com/albums/y218/Mr_Si/querysyntaxerror.jpg




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

Can you upload your database.

How To Upload A Database To The Forum (http://www.access-programmers.co.uk/forums/showthread.php?t=140587)

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

Drag, then what line of code does it highlight?

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

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

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

Hi George,

This is what it does:

http://i6.photobucket.com/albums/y218/Mr_Si/syntax-error-sitesearch.jpg

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

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.

Thanks namliam, that is the answer I needed.

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.

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

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

And ALT+F11 gets you into the VBA window without going thru the Module tab ....
more or less an alternative to CTRL+G

F11 brings up the database screen.