List of operators

Operators are available when you define the conditions for a query. The available operators depend on the type of field. For example, 'Yes or No' fields use a different range of operators to 'Text' fields.

Operators for text

You can use the following operators when you define conditions.

After; After or Equal to

If Value 1 is set to YOUNG (a surname), the results exclude YOUNG but include YOUNGER. To include YOUNG in the results, use the operator after or equal to.

Before; Before or Equal to

To find all license plates that start with the numbers 1 - 3 (in this example, the license plate starts with a maximum of three numbers).

Enter either before 4 or before or equal to 399.

Between

To find values that start with the letters Sch through Tho, you might enter between Sch Tho in Operator, Value 1, and Value 2. (The value in the Value 2 column is excluded from the results.)

Contains

To find records that contain the specific value.

For example, the word 'pistol' must be contained in a description of the 'Modus Operandi'. Use wildcards to find variations on the value.

Doesn't contain

To find records that exclude the specific value.

It is important to note that this operator does not return records where the specified field is blank. For example, if you specify that the description of the 'Modus Operandi' does not contain 'machete', the search returns only those records that contain a value but is not 'machete'. Records where the 'Modus Operandi' field is blank are not returned.

If you would like to return blank values as part of the results, add OR Modus Operandi is blank as an additional line to the query structure.

Ends with; Doesn't end with

For example, to find all license plates that end with the letters EW (the letter case is ignored):

ends with ew

To find values that start with a specific value and ending with the letters EW, you would need to use wildcards.

Equal to; Not equal to

Typically used to query values that were originally entered from a pick list - you select the value to query from the pick list. For example, use equal to when the field must exactly match the specified pick list value, such as the Vehicle Style must be Sedan.

Is blank; Isn't blank

Typically used to find values of fields that were not specified when the record was saved. Use isn't blank to search for records with any value in the specified field, or is blank to search for records that were left empty.

In list; Not in list

Finds values in a list of values you specify. For example:

  • Directly enter a list of values, separating them with the pipe | character. For example: US|UK|GB|.

  • Enter @ to be prompted for a list of values when the query is run.

You can also double-click Value 1 to:

  • Type or paste a list of value.

  • Browse for a text file that contains the required list of values.

If you type in the path, then you must enclose it in braces {}.

Is like; Isn't like

Finds exact values (unless you enter a wildcard). For example, a query on the surname YOUNG finds records that contain YOUNG, Young, or young.

Typically used to query values in a field where there might be variations in the spelling. The field must contain a wildcard value in Value 1. For example, *Homicide* finds records with Homicide and Scene of Homicide.

Starts with; Doesn't start with

Finds all records that contain fields that start with a specified value.

To find values that start with a specific value and contain or end with other values, you need to use wildcards.

Operators for multi-line text

You can use all the operators that are listed in Operators for text, except for:

  • After

  • After or equal to

  • Before

  • Before or equal to

  • Equal to

  • In list

Operators for numbers

You can use the following operators when you define conditions.

Between

Finds all values between, and including, the figures you enter in the Value 1 and Value 2 columns.

Equal to; Not equal to

Finds records where the field exactly matches the value that is given in the Value 1 column. Typically used to query values that are originally entered from a pick list.

Greater than; Greater than or equal to

Finds all values greater than the specified number. To include the specified number in the results, use the operator greater than or equal to.

Is blank; Isn't blank

Typically used to find values of fields that were not specified when the record was saved. Use isn't blank to search for records with any value in the specified field, or is blank to search for records that were left empty.

Less than; Less than or equal to

Finds all values less than the specified number. To include the specified number in the results, use the operator less than or equal to.

Outside

The opposite of the between operator. For example:

outside    10    50

finds all values less than 10 (exclusive) and more than 50 (inclusive).

Operators for dates, times, and time zones

You can use the following operators when you define conditions.

Note: When you define conditions that include time zones, you can use the equal to, or not equal to, and is blank, or not blank operators.

After

Finds all dates after the specified date (including the date you enter).

Note: When querying a date or time field, a comparison of after with the time portion of the date or time that is entered as 00:00:00 does not find any records for the specified date. You need to either enter the day before and exclude the time portion; or change the condition to same as or after; or set the time to 00:00:01.

Before

Finds all dates before the specified date (excluding the date you enter).

Between

Finds all dates between the two specified dates (including the dates you enter).

Day is; Day isn't

Finds all dates that fall on the specified day of the week (day is) or dates that fall on any day of the week other than the specified day (day isn't).

Different to

Finds records with any date or time value other than the entered value. This is equivalent to not equal to.

Is blank; Isn't blank

Finds records where the date or time zone was not specified when the record was saved (is blank) or where a date and time zone (any date and time zone) was specified (isn't blank).

Month is; Month isn't

Finds records with the specified month (month is) or any month other than the one specified (month isn't).

Outside

Finds records with a date or time outside the range of entered values (exclusive). This is the opposite of between.

Same as

Finds records with the specified date or time only. This is equivalent to equal to.

Same as or after

Finds records with the specified date or time, or later than the specified date or time.

Same as or before

Finds records with the specified date or time, or earlier than the specified date or time.

Year is; Year isn't

Finds records with the specified year (year is) or any year other than the one specified (year isn't).

Operators for yes or no fields

You can use the following operators when you define conditions.

Equal to

The field exactly matches the value that is given in the Value 1 column.

Not equal to

The field contains any value other than the value given in the Value 1 column.