Query on multiple fields

[josephbupe]

Please, I need to modify this query in the AfterUpdate event so that it can search on multiple fields in a table. Currently I only have one field LastName on which it is querying and I want to extend it so that it includes fields: FirstName, OtherNames and PassportNo.

If Me.txtSearch.Value <> "" Then
    Forms!F_Wanted_Treeview!F_Wanted_Persons_Visual.Form.Filter = "LastName Like '*" & Nz(Me.txtSearch.Value, "") & "*'"
    Forms!F_Wanted_Treeview!F_Wanted_Persons_Visual.Form.FilterOn = True
   Else
    Forms!F_Wanted_Treeview!F_Wanted_Persons_Visual.Form.FilterOn = False

I want to include all possible criteria behind the same txtSearch text box for currying out this operation. The reasons is that the txtSearch box is also used to query in the treeview control on the same form in which I have included a subform with image control to preview faces of persons as the treeview control updates.

Please, help.

Joseph

 

[JANR]

Build a Filterstring using the OR operator and apply this string to your formfilter.

ex:

Dim FilterString As String
FilterString = " Lastname Like '*" & Me.txtSearch & "*' OR Firstname Like '*" & Me.txtSearch & "'*" & _
               " OR OtherNames Like '*" & Me.txtSearch & "*' OR PassportNo Like '*" & Me.txtSearch & "*'"
 
Forms!F_Wanted_Treeview!F_Wanted_Persons_Visual.Form.Filter = FilterString
Forms!F_Wanted_Treeview!F_Wanted_Persons_Visual.Form.FilterOn = True

JR

 

[JANR]

Another option is that if yor form is based on a query you can add a calculated field in that query and concatinate your search fields using a pipe as a field delimiter, since it is unlikely that your data consist of that character.

ex.

MySearch: LastName & "|" & FirstName & "|" & OtherNames & "|" & PassportNo

The you can use your original code, just replace Lastname with MySearch

If Me.txtSearch.Value <> "" Then
    Forms!F_Wanted_Treeview!F_Wanted_Persons_Visual.Form.Filter = "[B][COLOR=red]MySearch[/COLOR][/B] Like '*" & Nz(Me.txtSearch.Value, "") & "*'"
    Forms!F_Wanted_Treeview!F_Wanted_Persons_Visual.Form.FilterOn = True
   Else
    Forms!F_Wanted_Treeview!F_Wanted_Persons_Visual.Form.FilterOn = False

JR