Adding complex criteria to queries

Usually, you can add the necessary criteria to a query using the standard criteria page. This page enables you to add multiple criteria to a query using either the AND or the OR operator, and enables you to change the order of the criteria. Sometimes, however, you will want to add criteria that are more complicated.

The Advanced button on the Criteria page enables you to enter a more complicated set of criteria, including the use of brackets.

To add complex criteria to a query:
  1. In Query Designer, open the required query, and display the Criteria page.
  2. Drag all of the attributes that you want to use in your criteria onto the Current Criteria list.

At this stage, it doesn't matter in which order you add the attributes, or which Relationship you choose - these will be reset when you set the Advanced criteria.

  1. Click Advanced.
    The Advanced Criteria Condition controls appear.
  2. In the Advanced Criteria Condition box, type the expression for the criteria condition you require, referring to the attributes by their Index in the Current Criteria list.

If you press the Spacebar in the Advanced Criteria Condition box, a menu appears showing you the available options. You can also use the buttons underneath the Advanced Criteria Condition box to build your expression.
To add brackets around part of your expression, select the required part of the expression, then click (---).

  1. When you have set the criteria, click Finish, and save the query.

When you redisplay the query in Query Designer, the Criteria page reflects the advanced criteria that you have designed.

Some of the standard queries (some Workload Lists, for example) have advanced criteria that you are prevented from changing. They are visible, but cannot be edited.

Examples:

The following examples demonstrate the difference that the brackets and order of the criteria make.

(1 AND 2) OR (3 AND 4)
To be included in the results, a record must meet:
either
both of the criteria 1 and 2
or
both of the criteria 3 and 4

1 AND (2 OR (3 AND 4))
To be included in the results, a record must meet:
criteria 1
and also meet
either
criteria 2
or
both of the criteria 3 and 4

2 AND (1 OR (3 AND 4))
To be included in the results, a record must meet:
criteria 2
and also meet
either
criteria 1
or
both of the criteria 3 and 4