Filter with BuildCriteria Function ~ LEARN MS-ACCESS TIPS AND TRICKS

Tuesday, December 15, 2009

Filter with BuildCriteria Function

Introduction

Any method that helps users find data quickly on a form is always appreciated. In Form View mode, several options are available to locate records efficiently.

When you right-click on a field, a shortcut menu appears, displaying four data filter options, as shown in the sample image below.

Right-click to open Large Image in New Tab/Window.

The third option, Filter For, accepts a criteria expression such as

>10200 AND <=10300

to filter a specific range of values from the selected field.

If you’d like to explore more filtering options, point to the Filter option on the Records menu. There, you’ll find two additional choices: Filter by Form and Advanced Filter/Sort.

Filter by Form allows you to set up criteria in multiple fields to select records based on values entered directly into form fields.

Advanced Filter/Sort opens the Filter Design (or Query Design) window, displaying the source table or query of the form, along with any criteria you may have already entered using Filter For or Filter by Form. You can then further modify your filter conditions, adjust the sorting order, and select Apply Filter from the Records menu to view the results.

However, if you’d like to build a custom filter option for the user—based on a specific field on the form—you can use the built-in BuildCriteria() function. By writing a VBA subroutine around this function and running it from a command button’s Click event, you can allow the user to input filter criteria in flexible ways.

The function usage is quite simple. Let’s try a few examples directly in the Immediate (Debug) Window to see how it works.

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

Sample Run of BuildCriteria() Function

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

Result: OrderID=10200

The BuildCriteria() function requires three parameters.

In the example, OrderID is the data field name, dbLong indicates that OrderID is a numeric field of type Long Integer, and the last value 10200 represents the criteria value used to select matching records.

The BuildCriteria() function automatically inserts the field name (OrderID) into the correct positions within the criteria expression.

The third parameter can be used in different ways depending on the result you want to achieve. Let’s explore a few more examples to better understand its flexibility before we implement this method on a form.

Type the following expressions in the Immediate (Debug) Window to see how the function behaves:

? 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#

Using on Form to Filter Data

After getting the result text from the BuildCriteria() Function, all we have to do is 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.

Import the Orders
Table from C:\Program Files\Microsoft Office\Officell\Samples\Northwind.mdb sample database.
Select the Orders Table and select Form from the Insert Menu.
Select Auto Form: Tabular to create a Form and save it with the name Orders.
Open the Orders Form in Design View.
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.
Display the Toolbox, if it is not visible (View ->Toolbox).
Select the Command Button Tool and create a Command Button on the Header of the Form.
While the Command Button is still in the selected state, display its Property Sheet (View -> Properties).
Change the Name Property Value to cmdFilter and change the Caption Property Value to OrderID Filter.
Display the Code Module of the Form (View -> Code).

Run it with a Button Click

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

Save and Close the Form.
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 the third Parameter into the BuildCriteria() Function above, when prompted for the Filter Condition.

Setting up Criteria Value on Text Box.

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 the Command Button.

The limitation of the BuildCriteria() Function is that it can accept only one field as the first parameter. But there is a way to use more than one field in 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 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)
    Loop
    Select Case resp
        Case 3 
           Exit Sub
        Case 1
            txtFilter = txtOrderfilter & " AND " & txtShipNameFilter
        Case 2
            txtFilter = txtOrderfilter & " OR " & txtShipNameFilter
    End Select
Else
    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

How does it work?

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

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

The Filter Strings are joined accordingly to obtain the intended result. The User doesn't need to always provide both sets of Criteria Values (for Orderld and ShipName) all the time. They can use only one Field for entering Criteria and ignore the other.

LEARN MS-ACCESS TIPS AND TRICKS

Tuesday, December 15, 2009