Text Search Filter Web Style

Introduction

How to search for several pieces of information across all the fields and in all the records of a Table?

For example, when we search for something on the web, we often enter multiple keywords separated by the Space character or the “+” symbol. The search engine then looks for any of the specified terms across web pages and displays the matching results on the screen.

Search text example 1: ms-access, forms, reports, queries

Or

Search text example 2: ms-access+forms+reports+queries

In the same way, we can create filters to display records from a table that match several pieces of Text/numbers/Phrases across any field in any record.

Last week, we learned how to use the BuildCriteria() function to filter data using a single field. The BuildCriteria() function accepts only one field as its first parameter. In this session, we’ll use the same function again — but with a clever trick that allows us to apply it to all the fields in a table.

A Simple Demo Run

So let us do this with a simple Query and a Tabular Form.

1. Import Customers Table from:
   

   C:\Program Files\Microsoft Offce\Office11\Samples\Northwind.mdb sample database.

2. Create a new Query based on the Customers table, including all its fields.
   Then, add a calculated column named FilterField to the Query.

3. Write the following expression in the new column to join all the Text and Numeric Field values together into a single Column:

   FilterField: [CustomerID] & " " & [CompanyName] & " " & [ContactName], etc., join all the fields this way except HyperLinks, Objects, and Yes/No Field Types. Save the Query with the name myQuery.

   You can automate this task using the following VBA program.
   Before running the code, create a Query manually that includes at least one field from the source table and name it myQuery.

   If you prefer to use a different Query name, simply modify the program wherever the reference to "myQuery" appears to match your chosen name.

   The CombineData() Code.

   Public Function CombineData(ByVal tblName As String)
   '----------------------------------------------------------
   'Author : a.p.r. pillai
   'Date   : December 2009
   'Rights : All Rights Reserved by www.msaccesstips.com
   '----------------------------------------------------------
   Dim strsql1 As String, db As Database, qrydef As QueryDef
   Dim fldName As String, k As Integer, j As Integer
   Dim tbldef As TableDef, strjoin As String
   
   On Error Resume Next
   
   strsql1 = "SELECT " & tblName & ".*, "
   Set db = CurrentDb
   Set qrydef = db.QueryDefs("myQuery")
   
   Set tbldef = db.TableDefs(tblName)
   k = tbldef.Fields.Count - 1
   
   strjoin = ""
   For j = 0 To k
       If tbldef.Fields(j).Type <> 1 And tbldef.Fields(j).Type <> 11 And tbldef.Fields(j).Type <> 12 Then
           If Len(strjoin) = 0 Then
               strjoin = "[" & tbldef.Fields(j).Name & "] "
           Else
               strjoin = strjoin & " & " & Chr$(34) & " " & Chr$(34) & " &  [" & tbldef.Fields(j).Name & "] "
           End If
       End If
   Next
   
   strsql1 = strsql1 & "(" & strjoin & ") AS FilterField FROM " & tblName & ";"
   qrydef.SQL = strsql1
   db.QueryDefs.Refresh
   
   Set tbldef = Nothing
   Set qrydef = Nothing
   Set db = Nothing
   
   End Function

   • Copy and paste the above VBA Code into a Standard Module and save it.

    Display the VBA Debug Window (Ctrl+G).

    Run the Program from the Debug Window by typing the following statement and pressing the Enter Key:

   CombineData "Customers"

   This program modifies the design of myQuery by combining all fields from the Customers table (or any other source table you specify), except for fields of the following types:

   • Hyperlink

   • Object (OLE Object)

   • Yes/No

   • Memo (Long Text)

   A new calculated column named FilterField will be created, which concatenates the values of all supported fields.

   If you also need to include Memo field contents, you must add that field manually in the Query Design view.
   The CombineData() Function intentionally skips Hyperlink, Object, and Memo fields because they all share the same internal data type category, and the program’s validation logic excludes them by design.

   The Sample Datasheet view image of the FilterField in myQuery is given below:

   

   Right-click to open Large Image in New Tab/Window.

   
   

   Design a Form

4. Create a Tabular Form (continuous form) using myQuery as Record Source and save the Form with the name frmMyQuery.

5. Open the form frmMyQuery in the Design view.

6. Select the FilterField Column and display its Property Sheet (View ->Properties)

7. Change the Visible Property Value to No.

8. Make the FilterField column size very small on the Form (it is not visible on normal view) and resize other Columns to view their contents properly.

9. Remove columns such as Region, Fax, or any other fields that you consider unnecessary for this test run.
   This will help ensure that all the required columns fit neatly on a single screen for easier viewing and testing.

10. Expand the Form Header Section and drag the column headings down so that we will get enough space to draw a TextBox and a Command Button beside it.

11. Create a Text Box above the column headings.

12. Display the Property Sheet of the Text Box (View ->Properties).

13. Change the following Property Values as given below:

    • Name = txtSearch
    • Width = 3"

14. Change the Caption of the Child Label of the Text Box to Search Text (delimiter:, or +):

15. Create a Command Button to the right of the Text Box and change the following Property Values:

    • Name = cmdGo
    • Caption = GO>
    
    

    The Form's Class Module Code.

16. Display the Code Module of the Form (View -> Code).

17. Copy and Paste the following VBA Code into the Module.

    Private Sub cmdGo_Click()
    '----------------------------------------------------------
    'Author : a.p.r. pillai
    'Date   : December 2009
    'Rights : All Rights Reserved by www.msaccesstips.com
    '----------------------------------------------------------
    Dim x_Filter, j As Integer
    Dim Y_Filter, Xchar As String, flag
    
    x_Filter = Nz(Me![txtSearch], "")
    
    If Len(x_Filter) = 0 Then
        Me.FilterOn = False
        Exit Sub
    End If
    'Code segment that tests and removes extra spaces'
    'between coma and next search text item.
    
    '--Extra space removal Segment start-
    Y_Filter = ""
    For j = 1 To Len(x_Filter)
        Xchar = Mid(x_Filter, j, 1)
    ' Test for presence of , or + and leading spaces
        If (Xchar = "," Or Xchar = "+") And Mid(x_Filter, j + 1, 1) = " " Then
                flag = True
        ElseIf Xchar = " " And flag Then
                flag = False
                Y_Filter = Trim(Y_Filter)
        End If
        Y_Filter = Y_Filter & Xchar
    Next
    x_Filter = Y_Filter
    
    '--Extra space removal Segment End-
    
    Y_Filter = "*"
    For j = 1 To Len(x_Filter)
            Xchar = Mid(x_Filter, j, 1)
         'Validation check
          If Xchar = "(" Or Xchar = ")" Then
                    MsgBox "Invalid Characters () in expression, aborted... "
                    Exit Sub
         End If
    If Xchar = "," Or Xchar = "+" Then
    'test for presence of ,+
         Xchar = "* OR *"
    End If
    Y_Filter = Y_Filter & Xchar
    Next
    
    Y_Filter = Y_Filter & "*"
    Me.FilterOn = False
    Y_Filter = BuildCriteria("FilterField", dbText, Y_Filter)
    Me.Filter = Y_Filter
    Me.FilterOn = True
    
    End Sub 

    The Sample Demo Run.

18. Save the Form and open it in Normal View.

19. Type some Text/Numbers/Phrases separated with commas (,) or plus symbol (+), which can match in any part of any Field(s) or in different Record(s), in the Text Box.

    Example 1: FRANK, Elizabeth Brown, Brazil

    Example 2: FRANK+Elizabeth Brown+Brazil

    Example 3: Frank Elizabeth Brown, Brazil

    Note: Do not use parentheses ( ) in the search text, as they can cause errors when processed by the BuildCriteria() function. Users might accidentally include extra spaces between the text separator (such as the + symbol) and the next search item; these extra spaces are automatically removed by the program before the search and filter operations begin.

20. However, spaces within phrases (embedded spaces) in the search text will remain intact.

21. Click on the GO> Command Button to search for the given text in fields/records and filter those records on the Form.

22. You may inspect the filtered records to ensure that one or more of the search texts you have entered into the Text Control appear in all the filtered records. They can appear in any field or fields in any Record, but all the records filtered will have these texts/numbers/phrases on them.

• Dynamic List Box Contents
• Office Assistant and MsgBox Menus-3
• Office Assistant and MsgBox Menus-2
• Office Assistant and MsgBox Menus
• Color and Picture in MsgBox