Skip to main content

Filter Expressions

  • 2 minutes to read

To filter data, you should create a filter expression(s). The simplest filter has the following syntax: “[Price] = 10”. This selects records that have 10 in the Price column.

The following table lists the available comparison operators:

Comparison Operator

Description

Example

=

Equal to.

The operator can be applied to numeric, date-time, string and Boolean values.

[ProductID] = 999

>

Greater than.

The operator can be applied to numeric, date-time and string values.

[ProductName] > 'Uncle'

<

Less than.

The operator can be applied to numeric, date-time and string values.

[UnitsInStock] < 100

>=

Greater than or equal to.

The operator can be applied to numeric, date-time and string values.

[BirthDate] >= #01/01/1980#

<=

Less than or equal to.

The operator can be applied to numeric, date-time and string values.

[CreatedDate] <= #10/2/2006#

<>

!=

Not equal to.

The operator can be applied to numeric, date-time, string and Boolean values.

[Priority] <> 'Low'

LIKE

Operator ‘LIKE’.

The operator is used for partial string comparison. It returns TRUE if the value being tested matches the specified pattern.

You can use the ‘%’ and ‘_’ symbols as wildcards in patterns. The ‘%’ wildcard substitutes any number of characters. The ‘_’ character is used to represent any single character.

[ProductName] LIKE '%Sauce%'

NOT LIKE

Operator ‘NOT LIKE’.

The operator is used for partial string comparison. It returns TRUE if the value being tested doesn’t match the specified pattern. See the description of the LIKE operator for more information.

[IssueSubject] NOT LIKE 'Help:%'

IS NULL

Checks whether the value being tested is the NULL value.

[Region] IS NULL

IS NOT NULL

Checks whether the value being tested is not the NULL value.

[Region] IS NOT NULL

Note

String constants must be enclosed within single quote characters. If a single quote character needs to be included as a literal to a filter it must be doubled. Example:

[ProductID] LIKE 'Uncle Bob''s%'

Note

Date-time constants must be wrapped with the ‘#’ character and represented using a culture-independent (invariant) format.

[CreatedDate] <= #07/30/2008#