A filter is made up of one or more conditions. A single condition compares a field to a value. For example, "Order Amount is greater than $100.00" is a condition that only allows those records with an order amount of more than $100.00 to appear in the report.

A condition is made up of either three or four parts, depending on whether it's the first condition in a filter or not: the field to filter on (for example, Order Amount), the operator, the value or values, and the connection.

Operator

How a field is compared to the value is called the operator. There are several operators available; they are shown in the table below. Some operators use more than one value; for example, is between requires two values (as in "Order Date is between 01/10/96 and 12/31/96"). Other operators do not use any value; for example, "Zip code is blank" is either true or not without needing a value.

Operator	Description
equals	The data stored in the field must exactly match the value you specify. Example: Last Name equals Johns The report shows only records with "Johns" as the last name. Records with "Johnston," "Johnson," and "Johnstone" are not included.
does not equal	The data stored in the field may be anything but the value you specify. Example: City does not equal Calgary
begins with	The data in the field starts with the value specified. Example: Last Name begins with Johns The report shows records with "Johns," "Johnston," "Johnson," and "Johnstone" as the last name. This operator only appears for character and memo fields.
does not begin with	The data in the field starts with anything but the value specified. Example: Last Name does not begins with Johns The report shows records other than those with "Johns," "Johnston," "Johnson," and "Johnstone" as the last name. This operator only appears for character and memo fields.
ends with	The data in the field ends with the value specified. Example: Last Name ends with son The report shows records with "Johnson" and "Clarkson" as the last name. This operator only appears for character and memo fields.
does not end with	The data in the field ends with anything but the value specified. Example: Last Name does not end with son The report shows records other than those with "Johnston" and "Clarkson" as the last name. This operator only appears for character and memo fields.
contains	The field has the specified value anywhere in the data it contains. Example: Outline contains Federal Gov All records with "Federal Gov" somewhere in the Outline field are included. This operator only appears for character and memo fields.
does not contain	The field does not have the specified value anywhere in the data it contains. Example: Outline does not contain Federal Gov This operator only appears for character and memo fields.
is blank	Includes all records where the specific field is empty or contains an unknown (null) value. Example: ZipCode is blank
is not blank	Includes records where the specific field contains data. Example: ZipCode is not blank
is known	Includes records where the specific field has any value. Example: ZipCode is known
is unknown	Includes records where the specific field has an unknown value, sometimes known as "null." Example: ZipCode is unknown
is greater than	Includes records where the specific field contains a value in excess of the specified value. Example: Amount is greater than 100 Example: City is greater than M
is greater than or equal	Includes records where the specific field contains a value in excess of or equal to the specified value. Example: Amount is greater than or equal to 100 Example: City is greater than or equal to M
is less than	Includes records where the specific field contains a value under the specified value. Example: Amount is less than 100 Example: City is less than M
is less than or equal	Includes records where the specific field contains a value under or equal to the specified value. Example: Amount less than or equal to 100 Example: CITY is less than or equal to M
is Yes	Includes records where the specific field contains "Yes." Example: Married is Yes This operator only appears for logical or Boolean fields.
is No	Includes records where the specific field contains "No." Example: Married is No This operator only appears for logical or Boolean fields.
is between	Includes records where the specific field contains values in a specified range. Example: Amount is between 150 and 250 Example: Date Started is between 05/01/94 and 12/31/94
is not between	Includes records where the specific field does not contain values in a specified range. Example: Amount is not between 150 and 250 Example: Date Started is not between 05/01/94 and 12/31/94
is one of	Includes records where the specific field contains one of up to ten specified values. The equivalent expression would be created using ten separate "equals" conditions. Example: City is one of Tokyo, Calgary, New York
is not one of	Includes records where the specific field does not contain one of up to ten specified values. The equivalent expression would be created using ten separate "does not equal" conditions. Example: City is not one of Tokyo, Calgary, New York
is	This operator, which only appears for date fields, allows you to choose a descriptive name for a date range. For example, if you choose "is" for the operator and "This Month" for the value, the filter condition includes records where the field is between the first and last days of the current month. Example: Order Date is This Year

Connection

Conditions are connected together with a connection. The connection can be either AND or OR. The AND connection means the two conditions it connects must both be true for a record to be included in the report, while the OR connection means that either one or both can be true for a record to be included.

You can think of AND as "more restrictive." For example, if the first condition is "City equals San Francisco," that gives a certain set of records. Adding a second condition such as "Order Amount is greater than 100.00" using the AND connection means that for any given record, the city must be San Francisco and the order amount must be greater than $100.00. Since that excludes San Francisco records with an order amount of less than $100.00 (which just specifying the first condition includes), these two conditions together are more restrictive (there are fewer records in the report) than either of these conditions alone.

You can think of OR as "less restrictive." For example, if the first condition is "City equals San Francisco" and you add a second condition such as "City equals Los Angeles" using the OR connection, this means that for any given record, the city can either be San Francisco or Los Angeles. These two conditions together are less restrictive than either of these conditions alone, since the report includes records matching both conditions.

Notice the use of AND and OR with filters can be the opposite of how these words are used in English. For example, someone might say "print out all of our customers from San Francisco and Los Angeles." You might think you use the AND connection in this case, but in fact you use the OR connection since you want records where the city is either San Francisco or Los Angeles. The way to think of it is this: for each record, check each condition. It is not possible, for example, for the city for any one record to be both San Francisco and Los Angeles at the same time. It could be either one, however, so OR is the correct connection to use.

Watch out if you need to use both AND and OR connections in a filter. Conditions connected with AND are evaluated before those connected with OR. For example, if you want all customers from San Francisco or Los Angeles where the sales amount is more than $50, you might think you could use "City equals San Francisco or City equals Los Angeles and Sales is greater than 50." However, this likely won't give you the results you want because the AND is evaluated first. So, this gives all customers from Los Angeles where the sales amount is more than $50 (the second and third conditions ANDed) or those from San Francisco regardless of the sales amount (the first condition).

If you need to use both AND and OR connections in a filter, you likely need to use parentheses with one or more of them. Parentheses determine the order of evaluation because conditions in parentheses are evaluated before anything else. So, to get all customers from San Francisco or Los Angeles where the sales amount is more than $50, use "(City equals San Francisco or City equals Los Angeles) and Sales is greater than 50." The way you specify the parentheses is to select ") AND" as the connection for the last condition (you don't have to specify the opening parenthesis; Stonefield Query inserts that automatically).

Here's a more complex example. A AND (B OR C) AND D means evaluate B OR C first (let's call the result E), then do A AND E (let's call that F), and finally F AND D. If the parentheses were left out, it evaluates A AND B (let's call that E), then C AND D (let's call that F), and finally E OR F.