The User Interface > Dialog/Control 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