Use the Selection Criteria tab to provide selection criteria for one or more columns in any table in the Access Definition Table List. The criteria is used to select rows to display, archive, or extract.

Abbreviation for the name in the Table box. Enter an easily typed substitute for the fully qualified table name to use in selection criteria or an SQL WHERE clause.
Character that is required to identify substitution variables in selection criteria and SQL WHERE clauses. To change the delimiter, click the down arrow.
Option for combining criteria for multiple columns:
CUSTNAME > 'M' AND STATE = ‘NJ'
CUSTNAME > 'M' OR STATE = ‘NJ'
Name of each column. You can rearrange the order in which the columns are displayed in the Table Editor or Point and Shoot Editor by dragging the grid row number. You cannot modify column names and you cannot search an SQL Variant column.
Selection criteria for any column.
Use an appropriate operator and value or substitution variable specification. Selection criteria must conform to SQL syntax and include relational or logical operators. Logical operators and syntax vary among DBMSs. Refer to the appropriate DBMS documentation for information. Lists of commonly used operators, appropriate to the current DBMS, are provided on the SQL tab.
For information on defining substitution variables, see Variables tab.
Validate selection criteria by clicking outside the grid. If the DBMS returns an error on an SQL Prepare statement, that message is displayed.
Remove selection criteria by right-clicking the Selection Criteria cell to open a shortcut menu and selecting Clear, Remove, or Remove All. You also can type over with blanks or use the Delete or Backspace key.
Date Criteria: A unique operator is used to select data based on values in a DATE column. The syntax for this operator is as follows:
BEFORE (nD nW nM nY)
Use the D, W, M, and Y arguments in any combination to indicate the number of days, weeks, months, or years that are subtracted from the date at run time. If no arguments are specified, the current date is used. Rows with a date older than the calculated date are extracted or archived. The n multiplier is an integer and can optionally be preceded by + or -.
A selection criteria icon
in
the Table Specifications column on the Access Definition
Editor Table List indicates that selection criteria are
specified for the table.
To obtain data for all customers with names that begin with the letter M, from the state of New Jersey, specify:
| Column | Criteria |
|---|---|
| CUSTNAME | LIKE 'M%' |
| STATE | = 'NJ' |
This example uses explicit values for each column. However, you can also use substitution variables.
For example, to obtain data for the same customers using a variable (ALPHA) for the alphabetic range and a variable (ST) for the state, specify:
| Column | Criteria |
|---|---|
| CUSTNAME | LIKE :ALPHA |
| STATE | = :ST |
Select AND to include all customers that satisfy both conditions. Select OR to include all customers that satisfy either condition.
To obtain data for all customers with names that begin with the letters M, N, O, P, and Q from the states of California, Arizona, and New Mexico, specify:
| Column | Criteria |
|---|---|
| CUSTNAME | BETWEEN 'M' AND 'Q' |
| STATE | IN ('CA','AZ','NM') |
As in the previous example, select AND to include all customers that satisfy both conditions. Select OR to include all customers that satisfy either condition.