Filter Function output in ListBox

Introduction

This FILTER is not related to a Query, SQL WHERE clause, or a Form’s Filter property. It is a built-in VBA function with a useful purpose: quickly filtering data from an array based on text matching.

We can use it to search across multiple fields of data from a Table, extract either matching or non-matching items, and display the results neatly in a List Box.

The Demo Run of the Filter.

But first, will experiment with a simple example to understand its usage. Copy and paste the sample VBA code given below into a Standard Module in your database:

Public Function myFilter()
Dim X(7) As Variant, Y As Variant
Dim J as Integer, msg as String

X(0) = "Strawberry Milk"
X(1) = "Chocolates"
X(2) = "Milkshake"
X(3) = "Mango Juice"
X(4) = "Icecold Milk"
X(5) = "Apple Juice"
X(6) = "Buttermilk"
X(7) = "Vanilla Icecream"

'Extract all items containing the text "milk" from Array X()
'and save the output in Array Y()
Y = FILTER(x, "milk", True, vbTextCompare)
msg = ""
For J = 0 To UBound(Y)
   msg = msg & J + 1 & ". " & Y(J) & vbCr
Next

MsgBox msg

End Function

Click anywhere within the Code and press F5 to run the Code. The output of the function will be displayed in a MsgBox.

How It Works

Let us examine the above code closely. Variable X is dimensioned for eight elements and loaded the Array with text values.

The FILTER() Function in the statement Y = FILTER(X, "milk", True, vbTextCompare) extracts the items that match the search text milk from the Source Array of values from Variable X and saves the output as an array of Values into Variable Y.

The FILTER() Function accepts four parameters.

The first parameter X is the Array containing the Text Values.

The second parameter  milk is the search text. It is compared against each item in the source array X. Whenever a match is found anywhere within an array element, that item is extracted and added as an element in the target array  Y .

The third parameter value True will extract the matched items as output and save them in Variable Y. When this value is set to False, the output will be items that do not contain the search text milk.

The fourth parameter dictates the use of a specific comparison method, like Binary, Database, or Text Comparison. Here, we have used the Text Comparison method. The third and fourth parameters are Optional.

Try the above Code with different search text, like juice or Ice, etc.

If you look at the Variable declarations of the Code, you can see that we have declared the Variable Y as a simple Variant Type and not as an Array Variable.

The FILTER() function automatically resizes the target array based on the results of the filter operation. Because the number of matches can vary with each search, the output array can be of different sizes each time. To handle this dynamically, we use the UBound() function to determine the number of elements in the filtered array. This allows a For...Next loop to iterate through all matching items and format them for display in a form MsgBox or other output.

Important points:

• The source array must be single-dimensional.

• The search text can be multiple pieces of information, like first name, last name, and address.  You should concatenate all relevant fields into a single string for each record and store them in a single-dimensional array.

The Filter() Function will not work in Code Modules of Form or Report.

Real Application Around Filter() Function

I have developed an Application around this Function for our Department Secretary to find Office Files with their location addresses (we have hundreds of them) that match a specific word or phrase in their Subject or Description Fields and display them in a List Box on a Form.

We will try a similar and simple example using employee data, using more than one field value, joined together as source Array contents, and display the Filter result in a List Box. I will give details of this example in the next Article.

In the meantime, you can experiment with using this function for your own tasks. Once I present my example, you can compare it with your approach and see the differences or improvements.

If you could do it differently, share the idea with me, so that I can learn something from you, too.

• Hiding Report Lines Conditionally-2
• Hiding Report Lines Conditionally
• Network and Report Page Setup-3
• Network and Report Page Setup-2
• Network and Report Page Setup