Criteria Operators

Criteria operators are filters you assign to the criteria fields to define the exact conditions a record must meet to appear in the query results.

For example, if you want to create a query of all invoices greater than $5,000, you can use the Invoice amount criteria field to filter the invoice information. If you want to see only invoices for greater than $5,000, use a criteria operator to specify that the Invoice amount is "greater than" $5,000.

  • Equals — Records must have the exact entry you define.

  • Does not equal — Records must not match the entry you define.

  • Greater than — Records must have a value greater than the one you define.

  • Greater than or equal to — Records must have a value greater than or equal to the one you define.

  • Less than — Limit records to those with a value less than the value you define.

  • Less than or equal to — Records must have a value less than or equal to the value you define.

  • One of — Records must have at least one of the entries you define.

  • Not one of — Records must not contain any of the entries you define.

  • Between — Records must fall within the range you define.

  • Not between — Records must not fall within the range you specify.

  • Blank — Records must be blank in the field you specify.

  • Not blank — Records must have an entry in the field you specify.

  • <Ask> — The “ask” operator means ask at runtime. This lets you postpone the selection of specific values for your criteria fields until the time you run the query.

    Note: Support for running queries with <Ask> operators will be available in a future release.

Combine operators

To narrow your query further, you can combine separate sets of criteria fields. Combining operators are characters that link criteria and define the records in the query:

Select And/Or to toggle between the two operators:

  • Use And between criteria fields to indicate records must meet both criteria to be selected.

  • Use Or between fields to indicate records can meet either criteria field.

Parentheses group criteria fields and provide structure to perform complex queries. Use parentheses if the query meets both of the following conditions:

  • The query includes three or more criteria fields.

  • The query uses both And and Or operators to link criteria fields.

Note: Be careful where you place parentheses in a query. If you change the location of the parentheses, the query results will also change.

For example, you may want to query for 1099 vendors with invoices greater than $600 or total of invoices over $1000. Select the 1099 Vendor, Invoice amount, and Total invoice amount criteria fields, then select operators and values for each criteria field:

  • 1099 Vendor equals Yes

  • Invoice amount greater than $600

  • Total invoice amount greater than $1,000

Then, combine the criteria fields with operators:

1099 Vendor equals Yes

AND

(Invoice amount greater than $600 OR

Total invoice amount greater than $1,000)