Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

Filter with BuildCriteria Function

Any method that helps to find data quickly on a Form is always welcome by Users. There are several options available when you are in Form View Mode.

When you Right-click on a Field a Shortcut Menu will popup showing four data filter options as shown on the sample image given below.

The third option Filter For can accept a Criteria Expression like >10200 AND <=10300 or similar to filter a Range of Values from the selected field.

If you would like to see more options then point on Filter option in Record Menu. There you can find two new Options: Filter by Form and Advance Filter/Sort. Filter by Form allows you to set up Criteria in more than one field to select records based on Form fields.

The Advance Filter/Sort will open up Filter Design (Query Design) Window with the Source Table/Query of the Form with whatever criteria you have entered into the Filter for or Filter by Form Options earlier, if any. You can further modify the filter conditions and Sorting Order and select Apply Filter from the Records Menu to view the result.

But, if you would like to build a Custom Filter Option for the User; based on a particular Field on the Form then you can use the built-in Function BuildCriteria() and write a VBA Sub-Routine around this Function to filter the data. Run the Sub-Routine on a Button Click Event Procedure and let the User input the Filter Criteria in various ways as he likes.

The Usage of the Function is very simple. Let us try few examples of the Function directly in the Debug Window (Immediate Window) to understand how it works.

  1. Press Alt+F11 to display the Visual Basic Editing Window.
  2. Press Ctrl+G to display the Debug window.
  3. Type the following example expressions and press Enter key to display the output:

? BuildCriteria("OrderID",dblong,"10200")

Result: OrderID=10200


The BuildCriteria() Function needs three Function Parameters. OrderID is the Data Field Name, dbLong indicates that OrderID is a Numeric Field with values of Long Integer Type and the last Value 10200 is OrderID Criteria value for selecting records. The OrderID Field Name will be inserted in appropriate locations in the Criteria expression by the BuildCriteria() Function.

The last parameter value we can use in several ways depending on how we want the result. Let us see few more examples before we implement this method on a Form. Type the following expressions in the Debug Window to see how it works:

? BuildCriteria("OrderID",dblong, ">=10200 AND <10300")

Result: OrderID>=10200 And OrderID<10300


? BuildCriteria("OrderID",dblong,">=10200 AND <10300 OR >=10400 AND <10500")

Result: OrderID>=10200 And OrderID<10300 Or OrderID>=10400 And OrderID<10500

Try changing the data type to dbText, for example:

? BuildCriteria("OrderID",dbText,"10200")

Result: OrderID="10200"

? BbuildCriteria("OrderDate",dbDate,">10/15/2009 and <=10/31/2009")

Result: OrderDate>#10/15/2009# And OrderDate<=#10/31/2009#

After getting the result text from the BuildCriteria() Function all we have to do is to insert it into the Filter Property of the Form and turn ON the Filter action.

Let us design a simple Form to Run our example straight away.

  1. Import the Orders Table from C:\Program Files\Microsoft Office\Officell\Samples\Northwind.mdb sample database.
  2. Click on Orders Table to Select it and select Form from Insert Menu.
  3. Select Auto Form:Tabular to create a Form and save it with the name Orders.
  4. Open the Orders Form in Design View.
  5. Expand the Form Header Area and drag all the Field Headings down to get enough room to create a Command Button above the Field Headings.
  6. Display the ToolBox, if it is not visible (View - ->Toolbox).
  7. Select the Command Button Tool and create a Command Button on the Header of the Form.
  8. While the Command Button is still in selected state display its Property Sheet (View - - > Properties).
  9. Change the Name Property Value to cmdFilter and change the Caption Property Value to OrderID Filter.
  10. Display the Code Module of the Form (View - -> Code).
  11. Copy and Paste the following VBA Code into the Module.
    Private Sub cmdFilter_Click()
    Dim txtCondition, txtFilter As String
    txtCondition = InputBox("OrderID Value/Range of Values")
    If Len(txtCondition) = 0 Then
        Me.FilterOn = False
        Exit Sub
    End If
    txtFilter = BuildCriteria("OrderID", dbLong, txtCondition)
    Me.FilterOn = False
    Me.Filter = txtFilter
    Me.FilterOn = True
    End Sub
  12. Save and Close the Form.
  13. You may click on the OrderID Filter Command Button and enter any of the examples (except the one with Date) criteria expressions we have entered as third Parameter into the BuildCriteria() Function above, when prompted for the Filter Condition.

If you don't like to use InputBox() to prompt for Criteria Values then you may create a Text Box on the Form where Users can enter the Criteria expression before hitting on the Command Button.

The limitation of the BuildCriteria() Function is that it can accept only one field as first parameter. But, there is a way to use more than one field for the Filter condition on the Form. Ask the User to enter conditions for two different fields separately and run the BuildCriteria() Function also separately to obtain the results. Join both results with AND/OR Logical operators to filter the data.

The following example Code uses OrderID and ShipName field values to Filter data on the Orders Form.

  • Create a Copy of the Orders Form and name the Form as Orders2.
  • Open the Form in Design View.
  • Display the Code Module of the Form ( View - -> Code).
  • Copy and Paste the following Code into the Module replacing the existing Code:
Private Sub cmdFilter_Click()
Dim txtOrderNumber, txtOrderfilter As String
Dim txtShipName, txtShipNameFilter As String
Dim msg As String, resp, txtFilter As String

txtOrderNumber = InputBox("OrderID Value/Range of Values to Filter")
txtShipName = InputBox("ShipName/Partial Text to Match")

If Len(txtOrderNumber) > 0 Then
    txtOrderfilter = BuildCriteria("OrderID", dbLong, txtOrderNumber)
End If

If Len(txtShipName) > 0 Then
    txtShipNameFilter = BuildCriteria("ShipName", dbText, txtShipName)
End If

If Len(txtOrderfilter) > 0 And Len(txtShipNameFilter) > 0 Then
    msg = "1. Filter items-that matches both filter conditions" & vbCr & vbCr
    msg = msg & "2. Matches either one or Both conditions" & vbCr & vbCr
    msg = msg & "3. Cancel"
    Do While resp <> 1 And resp <> 2 And resp <> 3
        resp = InputBox(msg)
    Select Case resp
        Case 3 
           Exit Sub
        Case 1
            txtFilter = txtOrderfilter & " AND " & txtShipNameFilter
        Case 2
            txtFilter = txtOrderfilter & " OR " & txtShipNameFilter
    End Select
    txtFilter = txtOrderfilter & txtShipNameFilter
    If Len(Trim(txtFilter)) = 0 Then
       Exit Sub
    End If
End If

Me.FilterOn = False
If Len(Trim(txtFilter)) > 0 Then
    Me.Filter = txtFilter
    Me.FilterOn = True
End If

End Sub

The first two InputBox() Functions collects the Filter Criteria for OrderID and ShipName Field values separately. Next steps Validates the User responses and Builds the criteria strings in txtOrderFilter and txtShipNameFilter Variables.

If both Variables have filter conditions in them then the User response is collected to check whether he/she needs the result set that meets both conditions (AND) or result set that meets any one of the conditions or both (OR).

The Filter Strings are joined accordingly to obtain the intended result. It is not necessary that the User should always provide both set of Criteria Values (for Orderld and ShipName) all the time. They can use only one Field for entering Criteria and ignore the other.


No comments:

Post a Comment


Your email address:

Delivered by FeedBurner


Infolinks Text Ads

blog.feedspot.com Microsoft Access Blogs

Popular Posts

Search This Blog

Blog Archive

Powered by Blogger.


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 Accesstips Menus and Toolbars MsaccessLinks Process Controls Art Work Downloads msaccess How Tos Graph Charts msaccessQuery Array List Boxes Command Buttons Emails and Alerts Property Query Class Module Combo Boxes Custom Wizards DOS Commands Data Data Type Objects VBA ms-access functions msaccess functions msaccess graphs msaccess reporttricks Calculation msaccessprocess security advanced Access Security Custom Functions Field Type Macros Menus Object Reference Report Top Values Variables msaccess email msaccess menus progressmeter Access2007 Auto-Number Command Button Copy Expression Fields Form Form Instances Join Methods Microsoft Numbering System Records Recordset Security Split SubForm Table Utility Workgroup database msaccess wizards Access2003 Accounting Year Action Animation Attachment Binary Numbers Bookmarks Budgeting ChDir Color Palette Conditional Formatting Controls Data Filtering Defining Pages Diagram Disk Dynamic Lookup Error Handler Excel 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 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

Access Class Module and Wrapper Classes

There are times that a particular Class Module needs to be instantiated more than once, to use similar set of values for a particular Applic...


Blog Archive

Recent Posts