Filtering in forms

I am compiling what seems to be a very simple database, but having not used access for a few years I am really struggling.The database contains only 1 table. Using only one form, all I want to do is be able to choose the field to search from a combo box, type the search criteria into a text box. Then hit a command button which will bring up all records which match the search criteria. After visiting my local library and taking out a stack of books I am still unable to do this and am wallowing in self pity.The following is the best piece of code that I have produced but still fails to work. Has anyone got any ideas?

Private Sub Command21_Click()
Dim strSearchField As String
Dim strSearchCriteria As String 'place search cirteria into strings

txtField.SetFocus 'combobox
strSearchField = txtField.Text

txtCriteria.SetFocus 'text box
strSearchCriteria = txtCriteria.Text

'apply the filter to the data

Me.Filter = "(strsearchfield) = (strsearchcriteria) & *"
Me.FilterOn = True

End Sub


Ideally I would like to include a wild card after "strsearchfield" to allow a user to type "A" and then bring up all records beginning in A, I have no idea if this is possible. Apologies if this code is laughable but it is the best I can do.

You have a bit of incoherent spaghetti in your code. Do you wish to filter the combo box or the record? These are two different things. Also, what is "& amp" at the end of your filter? Restate your post in clearer terms as to what you wish to do and then we'll alleviate your suffering . . . . until then, happy wallowing.

Apologies, here is the corrected code



txtCriteria.SetFocus

If txtCriteria.Text = "" Then

MsgBox "No criteria selected, all records displayed.", vbOKOnly
DoCmd.ShowAllRecords

Else

strSearchField = Me.txtField.Value
strSearchCriteria = txtCriteria.Value

'apply the filter to the data

Form.Filter = strSearchField & " Like '" & strSearchCriteria & "*'"
Me.FilterOn = True
Me.Requery 'this line may be superfluous

End If







strSearchField is from a combo box containing all field headings with selection limited to the list.

The user can select a field to search then type the criteria they which they to search for into "txtCriteria"

For fields containing only characters the filter works fine however the following error message appears for

the remaining fields that contain numbers.



"Syntax Error (missing operator) In Query Expression: Form.Filter = strSearchField & " Like '" & strSearchCriteria & "*'"



(that’s not quite the run time error but is hopefully easier to understand)



Has anyone got any suggestions?

Hello. I am having the same exact error as mentioned above, and none of the suggestions posted help.

Maybe someone has come across this before, and would be willing to share some light on the subject.

I have a form and would like the text FNAME = <inputted text>*
Alternativley FNAME = LIKE <inputted text>*

Anything that will show me all the results beginning with <inputted text>

This produces no errors and give me FNAME = <inputted text>
strFilter = "FNAME = '" & TFirst.Value & "'"
Me.SubFoundCustomers.Form.Filter = strFilter

If i manually go into the subform and change its forms filter to FNAME = <inputted text>* and then create a button to turn the FilterOn property to true, i get the correct results. But when i concat an *(in any logically place) i get the following error:

Run-time error '3075':
Syntax error (missing operator) in query expression 'FNAME = 't'*'.

I need help with this. Does anyone have any suggestions?

NaTe