A named filter allows you to filter the records in a table and to save the filter for future use. You may also filter according to the values in several fields at once. In addition, you can specify conditions for the filter in order to fine-tune the record selection. Named filters can be saved and re-applied whenever you like. Note that if you only wish to filter according to the values in a single field, it is easier and quicker to use your mouse (see Filtering on a single field).
To create a named filter for a table, first go into the table and then follow these steps:
Click on the Filter button on the Query toolbar (alternatively select Filter from the Format menu). The "Format Query" dialog is opened at the Filter page. The final filter expression will be contained in the Condition box but built up using the Condition Item box.
In the Condition Item group box you define a filter condition for each of the fields that is to be used in the filter. Ability takes the field you select and compares all the values in this field to the condition you specify for the filter. The filter selects only those records where the field value fulfils the specified condition.
Click on the arrow in the Field box to display the full list of fields in your table and then click on one of the fields to select it.
Click on the arrow in the Operator box to display the full list of operators that can be used to construct a condition. These include operators for:
Equals (=)
Contains ($)
Begins With (<$)
Ends With (>$)
Less Than (<)
Greater Than (>)
Less Than or Equal To (<=)
Greater Than or Equal To (>=)
Not Equal To (< >)
Is Empty ("")
Not Empty (!!)
In the Value box you type the value that is to be compared, using the operator, to the values in the field you selected in Field. Alternatively you can click on the arrow and choose a field from the drop-down list. In this case, the values of the field in Field will be compared to the values of the field in Value, for each record in the table.
For example, in a table that contains details on customers, you might select, say, Customer_Name in Field and < in Operator, and type S in Value. This would filter the table to show only the records where the Customer_Name begins with any letter less than S, that is any letter before S in the alphabet.
Next, you need to insert the condition you have specified into the Condition box. Click on the Insert button to do this.
You can construct more complex conditions by using the Start, And, Or and End buttons. These allow you to use And and Or to link condition expressions, and to control the order of their evaluation using opening and closing brackets (Start and End). Whenever you insert a condition into the Condition box, it is placed after the current contents of the box. Therefore, if you have already inserted a condition, you need to insert either an AND or an OR before inserting the next condition. Also, judicious use of brackets will ensure that complex condition expressions are evaluated properly. If you make a mistake, you can edit the condition expression in the normal way by right-clicking your mouse.
Once the conditions making up a filter are inserted and the relevant brackets have been placed, you can test the filter to check that it is well formed, that is to say, it follows the syntax rules or "grammar" for a condition expression. Click on Test. If the condition expression is well formed a message is displayed: "The Query is Correct!"; if it is not well formed, an error message is displayed with an indication of what the error is e.g. brackets don't match, syntax error, etc. Note that a well-formed filter expression is merely "grammatically" correct, but doesn't necessarily mean what you want it to mean. For that you need to make sure the filter is well designed according to the purposes you set for it.
You can update or replace a particular condition. Select the condition you want to replace, including its surrounding brackets, in the Condition box and create a new condition in the Condition Item box. Click on the Update button. The selected condition will be replaced by the new condition. Note that everything you select in Condition will be replaced by the one condition you specify in Condition Item, so be careful to select only the text that you want replacing before selecting Update.
To delete the contents of the Condition box as a whole, click on the Clear button. If you only want to delete selected text, first select the text and then right-click your mouse and select Delete (or press Del on the keyboard).
After you have constructed the filter from the constituent conditions and tested it to check that it is well formed, you can choose whether you want the filter to replace, act on top of or be added to a currently applied filter. You do this in the Filter Action box at the top right corner of the dialog. Click on the arrow and select one of Replace, And or Or from the drop-down list:
Replace is the default. Any filters currently applied to your table are ignored and the new filter is applied to all the records in the table.
And causes the new filter to be applied only to those records that are already selected by a currently applied filter.
Or causes the new filter to be applied to all the records in the table but will also leave selected any records that are already selected by a current filter.
Finally, you can give the filter a name in Name.
Click on OK to apply the filter. You will be asked if you want to create a new filter. Select Yes if you want the filter to be saved for future use under the name you have given it.
You are returned to Table Grid View with the filter applied and the filter name displayed in the Filter box on the Query toolbar.
When you next want to apply the filter, just click on the arrow in the Filter box and select the filter name from the drop-down list.
See: