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 mismatched 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. 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 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 is 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 the Text Comparison method. The third and fourth parameters are Optional.

You may 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.

But, this declaration is changed by the FILTER() Function and re-dimensions as an Array automatically, depending on the output of the filter action. Every time when we run the code with different search text parameters 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 pieces of information then join all of them together as a single string and load them 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 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 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 subject to moderation before publishing.




Post Feed


Popular Posts

Blog Archive

Powered by Blogger.


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