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.

OperatorDescription
equalsThe 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 equalThe data stored in the field may be anything but the value you specify.

Example: City does not equal Calgary

begins withThe 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 withThe 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 withThe 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 withThe 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.

containsThe 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 containThe 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 blankIncludes all records where the specific field is empty or contains an unknown (null) value.

Example: ZipCode is blank

is not blankIncludes records where the specific field contains data.

Example: ZipCode is not blank

is knownIncludes records where the specific field has any value.

Example: ZipCode is known

is unknownIncludes records where the specific field has an unknown value, sometimes known as "null."

Example: ZipCode is unknown

is greater thanIncludes 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 equalIncludes 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 thanIncludes 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 equalIncludes 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 YesIncludes records where the specific field contains "Yes."

Example: Married is Yes

This operator only appears for logical or Boolean fields.

is NoIncludes records where the specific field contains "No."

Example: Married is No

This operator only appears for logical or Boolean fields.

is betweenIncludes 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 betweenIncludes 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 ofIncludes 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 ofIncludes 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

isThis 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.


© Stonefield Software Inc., 2023 • Updated: 12/21/18
Comment or report problem with topic