Filter example

This example applies some of the principles of filter design explained in Filtering using a named filter.

Note: If you are new to filters, don't be put off by the length of the example. Since all of the conditions that go into making the filter are created in a similar manner, most of the example consists of "variations on a theme". Nevertheless, it should make clear to you the importance of thinking about the structure of a filter before actually creating it, the proper use of bracketing, and the difference between AND and OR.

Imagine you have a table, called Contacts, with three fields, viz. Name, Age, and Occupation. You want to construct a filter that will display all contacts who are lepidopterists and more than 70 years of age, and whose names are either Smith or Jones (perhaps you wish to invite a select company to a private viewing of your butterfly collection).

The problem is to find all records such that:

(Occupation = Lepidopterist)

AND

(Age > 70)

AND

(Name = Smith OR Name = Jones).

Note that the two ANDs are specified across fields, that is we want to select records which contain field values that satisfy the first condition AND the second condition AND the third condition together. The one OR, which occurs within the third condition, is specified down records, that is we want to select only those records that have either 'Smith' or 'Jones' in the Name field.

In the example, I have put AND and OR in capitals to give you an idea of the logical structure of the final condition expression. This requires us to create four conditions in total: three main conditions connected by ANDs, where the third condition is constructed out of two sub-conditions connected by OR.

In the Condition Item box, create the first condition:

  1. Click on the arrow in Field and select Contacts.Occupation.

  2. Click on the arrow in Operator and select = Equals.

  3. Click in the Value box and type Lepidopterist.

  4. Click on the Insert button to insert the condition into the Condition box below.

The expression inserted is:

(Contacts.Occupation = 'Lepidopterist').

Note that the expression, as a whole, is surrounded by brackets, the typed value is surrounded by single quotes, and the expression is selected.

  1. Next, you need to insert an AND. AND is inserted at the cursor position and will replace any selected text, so click at the end of the expression to deselect it and then click on the And button. This gives:

(Contacts.Occupation = 'Lepidopterist') AND.

  1. In the Condition Item box create the second condition:

  2. Select Contacts.Age in Field.

  3. Select > Greater Than in Operator.

  4. Type 70 in Value.

  5. Again click on Insert.

In the Condition box this gives:

(Contacts.Occupation = 'Lepidopterist') AND (Contacts.Age > 70).

Note that 70, as a numerical value, is not surrounded by quotes.

  1. Click at the end of the compound expression and click on And to get:

(Contacts.Occupation = 'Lepidopterist') AND (Contacts.Age > 70) AND.

In the Condition Item box you can now create the third and most complex condition:

  1. First, select Contacts.Name in Field and = Equals in Operator, and type Smith in Value.

  2. Click on Insert to insert the condition.

  3. In the Condition box click at the end of the compound expression and click on Or. This gives:

(Contacts.Occupation = 'Lepidopterist') AND (Contacts.Age > 70) AND (Contacts.Name = 'Smith') OR.

In the Condition Item box create the second part of the OR expression:

  1. Select Contacts.Name in Field.

  2. Select = Equals in Operator.

  3. Type Jones in Value.

  4. Click on Insert.

In the Condition box this gives:

(Contacts.Occupation = 'Lepidopterist') AND (Contacts.Age > 70) AND (Contacts.Name = 'Smith') OR (Contacts.Name = 'Jones').

All the relevant conditions have now been inserted to form a compound condition expression. However, you must now insert brackets in the right places to make sure that the expression is evaluated properly. At present a filter using this expression will correctly select all the Smiths who are lepidopterists aged over 70, but will incorrectly select all Joneses, no matter what their occupation or age. This is because the OR has not been restricted to work only within the scope of the third - joint - condition. Therefore, you need to surround the two conditions connected by OR by another pair of brackets.

  1. Click immediately before (Contacts.Name = 'Smith') and then click on Start ( to insert an opening (left-hand) bracket.

  2. Click immediately after (Contacts.Name = 'Jones') and then click on End ) to insert a closing (right-hand) bracket.

This gives you the final compound condition expression for your filter:

(Contacts.Occupation = 'Lepidopterist') AND (Contacts.Age > 70) AND ((Contacts.Name = 'Smith') OR (Contacts.Name = 'Jones')).

Rearranging it on the page should make the structure of the condition expression clearer:

(Contacts.Occupation = 'Lepidopterist')

AND

(Contacts.Age > 70)

AND

((Contacts.Name = 'Smith') OR (Contacts.Name = 'Jones')).

Compare this with the outline of the problem I gave at the start of this example.

Note that using Test will indicate a well-formed expression for both the correct version and the previous one with the extra brackets missing. This is because both expressions are perfectly legitimate, though they give different results. In addition, when I put in the extra brackets after inserting all the conditions, I did this to show you the difference brackets make to evaluation. There is nothing to stop you putting in extra brackets immediately they are needed.