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.

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 a 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.

Share:

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 __________________ [...]

    ReplyDelete

Translate



PageRank
Your email address:

Delivered by FeedBurner

Search

Infolinks Text Ads


Blogs Directory

Popular Posts

Search This Blog

Blog Archive

Powered by Blogger.

Labels

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

Featured Post

Function Parameter Array Passing

Last week we have explored the usage of ByVal (By Value) and ByRef (By Reference),  in the Function Parameter, to pass the value from  a Va...

Labels

Blog Archive

Recent Posts