Programming & Security

Posted: 18 Sep 2009 06:45 AM PDT

This FILTER is not related to a Query or WHERE clause in SQL or Filter settings on a Form. It is a built-in Function. Its usage is very interesting and it is useful to quickly filter out data from an Array of information through text matching. We will use this Function to search for values across more than one field of data from a Table, extract matched items or mismatch items, as the case may be, and display them in a List Box.

But first, let us look into 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 an MsgBox.

Let us examine the above code closely. The 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 matches with 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 value milk is the search text that is compared with each item of the Array of values in variable X and if a match found anywhere within the Array Item then extracts that item as output and adds it into an element of the target Array Variable Y.

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

The fourth Parameter asks the Filter Function to apply a specific comparison method like Binary Comparison, Database Comparison or Text Comparison method. Here, we have used Text Comparison method. Third and fourth parameters are Optional.

You may try the above Code with different piece of 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.

But, this declaration is changed by the FILTER() Function and re-dimensions it as an Array automatically, depending on the output of the filter action. Every time when we run the code with different search text parameter this can be different and unpredictable too. So, we have used the UBound() Function to find the number of elements in the output Array for the For…Next loop to take all the items and format a string to display the output items in the MsgBox.

The Source Variable must be a singly dimensioned Array. If your search text needs to be compared with several piece of information then join all of them together as a single string and load into the singly dimensioned array.

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

I have developed an Application around this Function for our Secretary to find all the Office Files with their location addresses (we have hundreds of them) that matches 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 with data taken from more than one field of the Employees Table, 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.

Till that time if you find you can use this function for some of your own tasks, you may do that. When I come out with my example you can compare yours with that and find the difference.

If you did it differently share your ideas with me, so that I can learn something from you too.