Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

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 give several pieces of text separated by, or + symbol to match any of the given text anywhere within the Web Pages and display the matching links on the screen.

Search text example1: ms-access, forms, reports, Queries

Or

Search text example2: ms-access+forms+reports+queries

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

Last week we learned the use of the BuildCriteria () Function to filter data using only one field. BuildCriteria() Function can accept only one Field as its first Parameter. We will be using this Function here also. But, we will pull a trick for using this Function for 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 Query using the Customers Table with all the fields and by adding a new Column with the name FilterField.

  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.

    This task you can automate with the following Program after creating a Query manually with at least one field from the Source Table and naming the Query as myQuery. You may modify the Program where the reference to myQuery name appears to implement your own preferred name if needed.

    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 Enter Key:

    CombineData "Customers"

    This will modify the design of myQuery by joining all the fields, except HyperLink, Object, Yes/No, and Memo Fields, from Customers Table, or any other Table that you use, and will create a new column with the name FilterField. If you need Memo Field contents too, then you must add that Field manually in Query design. The CombineData() Program will not do this because HyperLinks, Object & Memo fields fall into the same field Type category and the validation check bypasses it.

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


    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 the columns like Region, Fax, etc., or any other column that you feel are not required for our Test Runs so that all the required columns will fit on one screen properly.

  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.

    Example1: FRANK, Elizabeth Brown, Brazil

    Example2: FRANK+Elizabeth Brown+Brazil

    Example3: frank+ Elizabeth Brown, Brazil

    NB: Do not use brackets "()" in the search text. These will run into errors when used in the BuildCriteria() Function. Users may type extra spaces between the text separator character, or + symbol and the next search text item and these spaces will be removed by the program before going into search and filter operations. Embedded spaces in phrases will be left alone in the search text.

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

  21. You may inspect the filtered records to ensure that one or more of the search text you have entered into the Text Control appears 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.

Share:

6 comments:

  1. [...] a look at this Blog Post:LEARN MS-ACCESS TIPS AND TRICKS Blog Archive Text Search Filter Web Style __________________ http://www.msaccesstips.com (Learn MS-Access Tips & [...]

    ReplyDelete
  2. Change this line:

    ElseIf Xchar " " And flag Then

    with this:

    ElseIf Xchar = " " And flag Then

    ReplyDelete
  3. The Error line is corrected. '
    Thank you for pointing out the mistake.

    ReplyDelete
  4. [...] Answer:Take a look at this Blog Post:LEARN MS-ACCESS TIPS AND TRICKS Blog Archive Text Search Filter Web Style Answer:Hi apr [...]

    ReplyDelete
  5. I enjoy the way you capture the substance of the concept, really great writting style, I enojoyed it!

    ReplyDelete
  6. [...] you have rightly guessed the solution. Take a look at the following Article on this subject: Text Search Filter Web Style __________________ http://www.msaccesstips.com (Learn MS-Access Tips and Tricks) Learn Advanced [...]

    ReplyDelete

Comments subject to moderation before publishing.

PRESENTATION: ACCESS USER GROUPS (EUROPE)

Translate

PageRank

Post Feed


Search

Popular Posts

Blog Archive

Powered by Blogger.

Labels

Forms Functions How Tos MS-Access Security Reports msaccess forms Animations msaccess animation Utilities msaccess controls Access and Internet MS-Access Scurity MS-Access and Internet Class Module External Links Queries Array msaccess reports Accesstips WithEvents msaccess tips Downloads Objects Menus and Toolbars Collection Object MsaccessLinks Process Controls Art Work Property msaccess How Tos Combo Boxes Dictionary Object ListView Control Query VBA msaccessQuery Calculation Event Graph Charts ImageList Control List Boxes TreeView Control Command Buttons Controls Data Emails and Alerts Form Custom Functions Custom Wizards DOS Commands Data Type Key Object Reference ms-access functions msaccess functions msaccess graphs msaccess reporttricks Command Button Report msaccess menus msaccessprocess security advanced Access Security Add Auto-Number Field Type Form Instances ImageList Item Macros Menus Nodes RaiseEvent Recordset Top Values Variables Wrapper Classes msaccess email progressmeter Access2007 Copy Excel Export Expression Fields Join Methods Microsoft Numbering System Records Security Split SubForm Table Tables Time Difference Utility WScript Workgroup database function msaccess wizards tutorial Access Emails and Alerts Access Fields Access How Tos Access Mail Merge Access2003 Accounting Year Action Animation Attachment Binary Numbers Bookmarks Budgeting ChDir Color Palette Common Controls Conditional Formatting Data Filtering Database Records Defining Pages Desktop Shortcuts Diagram Disk Dynamic Lookup Error Handler External Filter Formatting Groups Hexadecimal Numbers Import Labels List Logo Macro Mail Merge Main Form Memo Message Box Monitoring Octal Numbers Operating System Paste Primary-Key Product Rank Reading Remove Rich Text Sequence SetFocus Summary Tab-Page Union Query User Users Water-Mark Word automatically commands hyperlinks iSeries Date iif ms-access msaccess msaccess alerts pdf files reference restore switch text toolbar updating upload vba code