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 required to identify substitution variables in selection criteria and SQL WHERE clauses. To change the delimiter, click the down arrow.
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 may also overtype with blanks or use the Delete or Backspace key.
Date Criteria: A unique operator allows you to select data on the basis of values in a DATE column. The syntax for this operator is:
Use the D, W, M, and Y arguments in any combination to indicate the number of days, weeks, months, or years subtracted from the date at runtime. 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 -.
The following examples use operators and syntax that may not be valid for all DBMSs. The functions demonstrated in the examples are universal, however.
Example 1: 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.
Example 2: 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.