---

Text Search Filter Web Style Posted: 20 Dec 2009 09:27 AM PST 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 piece of text separated with , 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 filter to display records from a table that matches several piece of Text/numbers/Phrases across in any field in any record. Last week we have learned the usage of 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. So let us do this with a simple Query and a Tabular Form. Import Customers Table from C:\Program Files\Microsoft Offce\Office11\Samples\Northwind.mdb sample database. Create a Query using the Customers Table with all the fields and by adding a new Column with the name FilterField. 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. and 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. 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 following 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 falls into the same field Type category and the validation check bypasses it. Sample Datasheet view image of the FilterField in myQuery is given below: Create a Tabular Form (continuous form) using myQuery as Record Source and save the Form with the name frmMyQuery. Open the form frmMyQuery in Design view. Select the FilterField Column and display its Property Sheet (View - ->Properties) Change the Visible Property Value to No. 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. Remove the columns like Region, Fax etc. or any other column that you feel not required for our Test Runs, so that all the required columns will fit on one Screen properly. Expand the Form Header Section and drag the column headings down so that we will get enough space to draw a Text Box and a Command Button beside it. Create a Text Box above the column headings. Display the Property Sheet of the Text Box (View - ->Properties). Change the following Property Values as given below: Name = txtSearch Width = 3" Change the Caption of the Child Label of the Text Box to Search Text (delimiter: , or +): Create a Command Button to the right of the Text Box and change the following Property Values: Name = cmdGo Caption = GO> Display the Code module of the Form (View - -> Code). 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 Save the Form and open it in Normal View. Type some Text/Numbers/Phrases separated with comas (,) 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, Brzail 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 in for search and filter operations. Embedded spaces in phrases will be left alone in the search text. Click on the GO> Command Button to search for the given text in fields/records and filter those records on the Form. 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 text/number/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

You are subscribed to email updates from LEARN MS-ACCESS TIPS AND TRICKS To stop receiving these emails, you may unsubscribe now.	Email delivery powered by Google
Google Inc., 20 West Kinzie, Chicago IL USA 60610