Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

Filter Function output in ListBox


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.

The Demo Run of Filter

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

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. The Variable X is dimensions 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.

Real Application Around Filter() Function

I have developed an Application around this Function for our Department 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.


1 comment:

  1. [...] for your issue, but if you would like to approach your problem then the links are given below: LEARN MS-ACCESS TIPS AND TRICKS - Filter Function output in ListBox LEARN MS-ACCESS TIPS AND TRICKS - Filter Function Output In Listbox-2 __________________ [...]


Comments are subject to moderation before publishing.


Subscribe in a reader
Your email address:

Delivered by FeedBurner

Blog Directory Visit blogadda.com to discover Indian blogs


Popular Posts

Search This Blog

Blog Archive

Powered by Blogger.

Follow by Email


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

Featured Post

ROUNDDOWN Function of Excel

We have introduced ROUNDUP() Function of Excel in Microsoft Access earlier.  It works like ROUND() Function except that the number is alway...


Blog Archive

Recent Posts