Additional Criteria in Query Forms

On the Additional Criteria tab of a query form, you can specify search criteria for any of the fields on the associated multiview form.

NOTE: This tab includes the fields that are listed on the Primary Criteria tab. This means that you are not obligated to specify primary criteria before doing a query.

One reason for using the Additional Criteria tab for the principal fields, rather than using the Primary Criteria tab is if you want to do a Boolean OR search, rather than a Boolean AND search on those fields. The Primary Criteria tab only allows AND searches.

Query Clauses

Query criteria are made up of query clauses. A query clause consists of a Boolean search delimiter, a field name, an operator, and a value. For example, to retrieve records for vendors located in New York, you would create a clause similar to the following:

AND State = 'NY'

where

• State is a field name on the multiview form in which vendors are defined in your system.
• = is an operator specifying that returned results must match the value exactly.
• NY is the value that you want the system to search for in the State field.

NOTE: The letter N might appear between the operator and the value in the clause. This N indicates that the system is using Unicode (double-byte) character codes.

Multiple clauses, if you use them, work sequentially. That is, the system searches using the first defined clause, then applies the second clause to the returns, and then the third clause, and so forth. This means that the order in which you define your clauses must be planned carefully, as order can make a difference.

AND and OR (Boolean) Searches

You can create multiple clauses and join them by Boolean AND or OR filters. Clauses joined by AND retrieve only those records that meet the criteria specified in all clauses. Clauses joined by OR retrieve records that meet any one of the criteria.

The system adds the Boolean filter automatically when you define the query clause. If you select the check box labeled OR instead of AND with previous clause, the clause is defined as an OR clause. If that check box is cleared, the system defines the clause as an AND clause.

To specify additional criteria:

1. In a query form, select the Additional Criteria tab.
2. From the first drop-down list box, select a field on which you want to filter.
3. In the second drop-down list box, select the operator you want to use.

   For more information about the operators available, see Operators on Query Forms.

4. In the third field, enter a value that you want to use as the search criteria. Remember that:
   • You can use the wildcard character to match unspecified characters.
   • To find records containing a null value in the field, you can enter the Null keyword.
   • To use the Null keyword, the operator must be = (equals). To find records in which the field is not null, use the Null keyword with the <> (not equal) operator.

   For more information about the Null keyword, see Using the Null Keyword.

   • To find records compared to the current date, you can use the CURDATE() keyword. See Sample Queries for more information.
   • Alphabetic input is not case-sensitive.
5. Specify whether to join the current query clause with the previous clause by AND or OR:
   • To use OR, select the check box labeled OR instead of AND with previous clause.
   • To use AND, clear the check box labeled OR instead of AND with previous clause.
   • If the current clause is the first clause you have defined for the query form, accept the default, which is AND.
   • If you specified any primary criteria, be sure to select AND or OR according to how you want to join the current clause with the primary criteria.
6. Click Add. The system adds the clause to the list.
7. Repeat steps 2-6 for each additional clause.

To remove a clause from the list:

1. In the list of clauses, select the one you want to remove.
2. Click Remove.

Related Topics

About Query Forms

Buttons on Query Forms

Finding Records Using a Query Form

Operators on Query Forms

Primary Criteria in Query Forms

Sample Queries

Understanding Filters

Using Wildcard Characters

Using the Null Keyword