ACC: How to Dynamically Create a Filter

• Microsoft Access versions 1.0, 1.1, 2.0, 7.0, 97

SUMMARY

Moderate: Requires basic macro, coding, and interoperability skills.

This article describes a method of dynamically creating a filter by applying expressions on a form using a macro. The same technique can also be adapted to a Query by Form (QBF) method.

NOTE: Although this article will work with Microsoft Access 7.0 and 97, a new feature has been introduced called Filter by Form. For more information on this new feature, query on the words "Filter by Form" using the Microsoft Access 97 Help Index.

MORE INFORMATION

The "+" and "&" operators are used to concatenate (join) two string values. However, each operator behaves differently when used on Null- valued strings. For example, if you use a Null variable "Name" in the expression "Dear "+[Name], then the expression evaluates to Null, whereas if you have the expression "Dear "&[Name], then the expression evaluates to "Dear ."

You can use these different behaviors to build a string for use in the Where condition of an ApplyFilter or OpenForm macro action. You combine the strings using the + operator to concatenate values to dynamically build each separate part of the filter (which becomes Null if no value is entered), and then use the & operator to concatenate the separate strings.

To dynamically create a filter by applying expressions on a form by using a macro, follow these steps:

1. Open the sample database Northwind.mdb (or NWIND.MDB in versions 1.x, 2.0).

2. Open the Employees form in Design view.

3. Click Save As on the File menu and save the form as Employees 2.

4. Widen the Employees 2 form and add the following controls to the new blank area in the detail section to the right of the photo.

   NOTE: The labels should be to the left of their corresponding text boxes.

         Label
         ------------------
         Caption: Search ID
   

         Text Box
         -------------------------------
         Name: SID
         ControlSource: <leave blank>
         AfterUpdate: Filter.Make null 2
   

         NOTE: In versions 1.x, the Name property is called the ControlName
         property.
   

         Label
         --------------------------
         Caption: Search First Name
   

         Text Box
         -----------------------------------------------------
         Name: SName
         ControlSource: <leave blank>
         Validation Rule: Not Like "*'*" Or Is Null
         Validation Text: Can't use apostrophe in filter text.
         AfterUpdate: Filter.Make null
   

         NOTE: In the following example, an underscore (_) is used as a
         line-continuation character. Remove the underscore when re-creating
         this example.
   

         In Microsoft Access 7.0:
   

         Text Box
         -------------------------------------------------------------------
         Name: Where Text
         ControlSource: =Mid((" And [Employee ID]="+[SID]) & _
                                (" And [First Name] Like '"+[SName]+"'"),2)
   
         In Microsoft Access 1.x, 2.0 and 97:
   
         Text Box
         -------------------------------------------------------------------
         Name: Where Text
         ControlSource: =Mid((" And [Employee ID]="+[SID]) & _
                                (" And [First Name] Like '"+[SName]+"'"),6)
   
         Command Button
         ---------------------------
         Caption: Apply Filter
         OnClick: Filter.Apply Filter
   
         NOTE: In versions 1.x, the OnClick property is called the OnPush
         property.
   
   

5. Close and Save the form.

6. Create the following macro group:

         Macro Group: Filter
         --------------------------------------------------------------
         Macro Name: Make null
            Condition:  IsNull(Screen.ActiveControl)
            Action: StopMacro
            Description: This macro is for text fields.
   
            Condition: Len(Screen.ActiveControl)=0
            Action: SetValue
            Item: Screen.ActiveControl
            Expression: Null
            Description: Make an empty text field null.
   
         Macro Name: Make null 2
            Condition: IsNull(Screen.ActiveControl)
            Action: StopMacro
            Description: This macro is for numeric fields.
   
            Condition: Not IsNumeric(Screen.ActiveControl)
            Action: SetValue
            Item: Screen.ActiveControl
            Expression: Null
            Description: Turn non-numeric values into nulls.
   
         Macro Name: Apply Filter
            Condition: IsNull([Where Text])
            Action: ApplyFilter
            Where Condition: True
            Description: No Where Text, so select all records and stop.
   
            Condition: ...
            Action: StopMacro
   
            Condition: Len([Where Text]) > 255
            Action: MsgBox
            Message: You have selected too many criteria in the filter.
            Title: Apply Filter
            Description: If filter too long, display warning and stop.
   
            Condition: ...
            Action: StopMacro
   
            Action: ApplyFilter
            Where Condition: =[Where Text]
            Description: Apply the filter.
   
   

7. Save the macro as Filter.

8. View the form in Form view and enter values in the search fields.

Limitations

• The length of the ControlSource property for the Where text box cannot exceed 255 characters. Microsoft Access will not let you type an expression longer than this. One workaround to this limitation is to use several text boxes to build each part of the criteria, and then use a final text box to combine the various parts into a single Where clause.
• The string that the expression produces cannot exceed 255 characters. The second test in the Apply Filter macro above tests for this condition. If the filter string is longer than 255 characters, you will receive a warning that the filter is too long and the macro will stop.