Filter a query with partial info

[Leesha]

Hi,

I have a user that needs to filter insurance poilcy numbers and she would like to be able to only put in part of the number and have the query sort for all records that begin with the numbers she enters. The field is formatted as a text field as there may be letters in the policy number. She will be entering the policy number or portion of the number in a texbox name policynumber on frmCertificationOfInsurance. I'm thinking I need to use a like statement but can't get it to work correctly.

Thanks,
Leesha

[HansV]

Let's say that you create an unbound text box txtFilter (I wouldn't give it the same name as the field, since this text box is unbound) and a command button cmdFilter on the form (for example in the form header, and that the field you want to filter on is called PolicyNumber.

Create the following event procedure for the On Click event of the command button:

Code: Select all

Private Sub cmdFilter_Click()
    Me.Filter = "[PolicyNumber] Like " & Chr(34) & Me.txtFilter & "*" & Chr(34)
    Me.FilterOn = True
End Sub

This will filter the records in the form.

If you need a query that filters the records, set the Criteria for the PolicyNumber column to

Like [Forms]![frmCertificationOfInsurance]![txtFilter] & "*"

[Leesha]

Works perfectly! Thanks so much Hans.
Leesha