Syntax error in a form

[Lifeseeker]

Hi,

I am testing out a sample database, but I'm getting no where with it.

Basically it's a form-based search on a main form. When you click on the search button, Access brings up a search form. On the search form you can search for a string in a chosen field. Hit Search and on the main form it's supposed to filter the records based on the search.

I keep getting this error message after I click on the "search" button the search form.

"syntax error (Missing operator) in query expression 'fee description LIKE '*d*".

I have attached the sample database, with the following VBA code: this code is for the "search button" on the search form.


Private Sub Command6_Click()

If Len(cbosearchfield) = 0 Or IsNull(cbosearchfield) = True Then
MsgBox "You must select a field to search."

ElseIf Len(txtsearchstring) = 0 Or IsNull(txtsearchstring) = True Then
MsgBox "You must enter a search string."

Else

'Generate search criteria
GCriteria = cbosearchfield.Value & " LIKE '*" & txtsearchstring & "*'"

'Filter frmCustomers based on search criteria
Form_qry_primary.RecordSource = "select * from lab where " & GCriteria
Form_qry_primary.Caption = "lab (" & cbosearchfield.Value & " contains '*" & txtsearchstring & "*')"

'Close frmSearch
DoCmd.Close acForm, "frm_Search"

MsgBox "Results have been filtered."

End If


End Sub


thank you for taking the time to look at it.

 

[BobMcClellan]

Hi,

I am testing out a sample database, but I'm getting no where with it.

Basically it's a form-based search on a main form. When you click on the search button, Access brings up a search form. On the search form you can search for a string in a chosen field. Hit Search and on the main form it's supposed to filter the records based on the search.

I keep getting this error message after I click on the "search" button the search form.

"syntax error (Missing operator) in query expression 'fee description LIKE '*d*".

I have attached the sample database, with the following VBA code: this code is for the "search button" on the search form.


Private Sub Command6_Click()

If Len(cbosearchfield) = 0 Or IsNull(cbosearchfield) = True Then
MsgBox "You must select a field to search."

ElseIf Len(txtsearchstring) = 0 Or IsNull(txtsearchstring) = True Then
MsgBox "You must enter a search string."

Else

'Generate search criteria
GCriteria = cbosearchfield.Value & " LIKE '*" & txtsearchstring & "*'"

'Filter frmCustomers based on search criteria
Form_qry_primary.RecordSource = "select * from lab where " & GCriteria
Form_qry_primary.Caption = "lab (" & cbosearchfield.Value & " contains '*" & txtsearchstring & "*')"

'Close frmSearch
DoCmd.Close acForm, "frm_Search"

MsgBox "Results have been filtered."

End If


End Sub


thank you for taking the time to look at it.

Change
this : GCriteria = cbosearchfield.Value & " LIKE '*" & txtsearchstring & "*'"

to this: GCriteria = "[" & cbosearchfield.Value & "]" & " LIKE '*" & txtsearchstring & "*'"

and your combo should have lab_num;lab_description

hth,
..bob

 

[Lifeseeker]

Change
this : GCriteria = cbosearchfield.Value & " LIKE '*" & txtsearchstring & "*'"

to this: GCriteria = "[" & cbosearchfield.Value & "]" & " LIKE '*" & txtsearchstring & "*'"

and your combo should have lab_num;lab_description

hth,
..bob

Great it works out well. Thank you Bob.

One more thing........how do I cancel out of the filter once I am done viewing the filtered results? Do I have to close and re-open the form each time?

Thanks

 

[BobMcClellan]

Great it works out well. Thank you Bob.

One more thing........how do I cancel out of the filter once I am done viewing the filtered results? Do I have to close and re-open the form each time?

Thanks

I usually use something like....
Forms![Your Form].form.Filter = ""
Forms![Your Form].form.FilterOn = false
hth,
..bob