Advanced Filter and Search Concepts

<< Click to Display Table of Contents >>

Navigation:  Application Layouts > Grids Views >

Advanced Filter and Search Concepts

Filter Expressions

A filter expression is a formula (or a set of formulas) that specifies how data should be filtered. Each expression contains three parts:

a data field whose values should be filtered;

a filtering value that should be compared to records stored in the data field;

an operator that compares data field values with a filtering value.

For example, the following expression selects all the "Count" data field values that are greater than 5 but less than 20:

[Count] Between ('5', '20')

Filter Expression Syntax

The table below enumerates most frequently used operators.

Operator

Description

Example

=

Equals. Selects data field values that equal the entered filtering value.

[OrderDate] = #2016-01-01#

<>

Does not equal. Selects data field values that are not equal to the entered filtering value.

[OrderDate] <> #2016-01-01#

>

Is greater than. Selects data field values that are greater than the entered filtering value.

[OrderDate] > #2016-01-01#

>=

Is greater than or equal to. Selects data field values that are greater than the entered filtering value or equal to it.

[OrderDate] >= #2016-01-01#

<=

Is less than or equal to. Selects data field values that are less than the entered filtering value or equal to it.

[OrderDate] >= #2016-01-01#

<

Is less than. Selects data field values that are less than the entered filtering value.

[OrderDate] >= #2016-01-01#

Between

Is between. Selects data field values that belong to the specific value interval.

[CustomerID] Between ('1', '100')

Not Between

Is not between. Selects data field values that lie outside the specific value interval.

Not [CustomerID] Between ('1', '100')

Contains

Contains. Selects data field values that contain the filtering value.

Contains([ShipCountry], 'land')

Not Contains

Does not contain. Selects data field values that do not contain the filtering value.

Not Contains([ShipCountry], 'land')

Starts with

Begins with. Selects data field values that start with the filtering value.

StartsWith([ShipCountry], 'G')

Ends with

Ends with. Selects data field values that ends with the filtering value.

Ends with([ShipCountry], 'ia')

In

Is any of. Selects data field values that equal any of the entered filtering values.

[ShipCountry] In ('Germany', 'Italy', 'USA')

Not In

Is none of. Selects data field values that do not equal any of the entered filtering values.

Not [ShipCountry] In ('Germany', 'Italy', 'USA')

Like

Is like. Selects data field values that contain the filtering value.

Accepts wildcards: '_' to replace a single character, '%' to replace any number of characters.

[OrderDate] Like '%2011'

same as

Contains ([OrderDate], '2011')

Not Like

Is not like. Selects data field values that do not contain the filtering value.

Accepts wildcards: '_' to replace a single character, '%' to replace any number of characters.

Not [OrderID] Like '103__'

same as

Not [OrderID] Between ('10300', '10399')

Is Null

Is null. Selects null values.

[ShipRegion] Is Null

Is Not Null

Is not null. Excludes null values.

[ShipRegion] Is Not Null

 

String values 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 (e.g., [ProductID] LIKE 'Uncle Bob''s%');

Date-time values must be wrapped with the '#' characters and represented using a culture-independent (invariant) format. The invariant culture is based on the English culture, but some of the idiosyncratic English formats have been replaced by more globally-accepted formats. Below are some of the culture-independent formats for representing date-time values.

MM/dd/yyyy — 07/30/2008

dd MMM yyyy — 30 JUL 2008

yyyy-MM-dd — 2008-07-30

yyyy-MM-ddTHH:mm:ss — 2008-07-30T22:59:59

yyyy-MM-dd HH:mm:ssZ — 2008-07-30 15:59:59Z