Article ID: 109334
Article Last Modified on 1/18/2007
APPLIES TO
- Microsoft Access 1.0 Standard Edition
- Microsoft Access 1.1 Standard Edition
- Microsoft Access 2.0 Standard Edition
- Microsoft Access 95 Standard Edition
- Microsoft Access 97 Standard Edition
This article was previously published under Q109334
Moderate: Requires basic macro, coding, and interoperability skills.
SUMMARY
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:
- Open the sample database Northwind.mdb (or NWIND.MDB in versions 1.x, 2.0).
- Open the Employees form in Design view.
- Click Save As on the File menu and save the form as Employees 2.
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.- Close and Save the form.
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.- Save the macro as Filter.
- 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: kbhowto kbusage KB109334
