ACC: How to Dynamically Create a Filter

ID: Q109334


The information in this article applies to:
  • 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.


Note that the data for the Apply Filter's Where condition is constructed in the Where text control. If you do not want users to see this, set the Visible property of the Where text field to False.

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.


To modify the above example for a QBF method, add the controls to a blank form not based on any table or query and change the ApplyFilter actions to OpenForm actions. To select all the records using OpenForm actions, leave the Where condition blank instead of using True.

Additional query words: dynamic QBE query by example

Keywords : kbusage McrActn
Version : 1.0 1.1 2.0 7.0 97
Platform : WINDOWS
Issue type : kbhowto


Last Reviewed: March 30, 1999
© 2000 Microsoft Corporation. All rights reserved. Terms of Use.