Types of Conditions
Conditions represent relationships between fields and values. For example, suppose you want to view all customers who have made purchases of $100 or more. You would need to establish a relationship between the Purchase Amount field and the value 100 (the relationship being that the Purchase Amount is greater than or equal to 100).
Conditions may be any of the following:
Number of Values
State = 'CA'
Not Equal To
State <> 'CA'
Quantity > 3
Quantity < 3
Greater Than or Equal To
Quantity >= 3
Less Than or Equal To
Quantity <= 3
Age BETWEEN 20 AND 30
Age NOT BETWEEN 20 AND 30
1 & Up
Status IN ('A','I','P')
1 & Up
Status NOT IN ('A','I','P')
Zipcode LIKE '207%'
Zipcode NOT LIKE '207%'
Title IS NULL
Is Not Null
IS NOT NULL
Title IS NOT NULL
EQUAL/NOT EQUAL: Tests for exact matches. The two values being compared must be identical for this condition to be true. NOT EQUAL is true when values are not identical.
GREATER THAN/LESS THAN: Ensures that the rows selected are greater than or less than the entered value. For numeric fields, standard arithmetic rules are used. For character data, an alphabetical comparison is used (e.g., A is less than B).
BETWEEN/NOT BETWEEN: Used to test for a range of values. For example, ACCOUNT_NUMBER BETWEEN 12345 AND 54321 asks for all of the account numbers between, and including, those two named. Likewise, ACCOUNT_NUMBER NOT BETWEEN 12345 AND 54321 asks for all of the account numbers except for the ones between, and including these two.
IN/NOT IN: Used to test for one or several values. For example, STATE IN ("ME","VT","NH") asks for information only in Maine, Vermont, and New Hampshire. NOT IN excludes values. STATE NOT IN ("ME","VT","NH") returns information from all states except Maine, Vermont, and New Hampshire.
IS NULL/IS NOT NULL: Searches for null (or missing) values. If you use IS NULL or IS NOT NULL, you cannot enter a value. This menu option is not available if the selected Field does not support nulls.
LIKE/NOT LIKE: These conditions are useful for doing wild card searches but are only available for character searches. The LIKE operator lets you search character data using partial matches. For example, you might want to retrieve all names containing the word "corporation."
Wildcard Character: The wild card character is a percent sign (%), which causes the comparison to skip over (or ignore) zero or more characters. Just as a wildcard in a poker game can represent any card, the % wildcard can be any character(s). Possible wildcard searches:
- ZIPCODE LIKE '609%' - searches for every ZIP code that begins with "609."
- NAME LIKE '%CORPORATION%' - retrieves all records that contain the string "CORPORATION" anywhere within the name.
- KEYCODE NOT LIKE 'D%' - retrieves all records that do not have keycodes starting with "D."