TCustomADODataSet.Filter Property

Specifies the text of the current filter for a recordset.

Pascal

property Filter: string;

C++

__property AnsiString Filter;

Description

Use Filter to specify a recordset filter. When filtering is applied to a recordset by setting the Filtered property to true, only those records that meet a filter’s conditions are available to the application. Filter contains the expression used to evaluate the rows in the recordset. At design-time, enter the filter expression into the editing cell for the Filter property within the Object Inspector. At runtime, assign a string containing the filter expression to the Filter property.

The filter expression below displays only those rows where the State field is "CA" or "MA":

Copy Code

State = 'CA' OR State = 'MA'

The following runtime example shows how to assign that filter expression to the Filter property and activate the filtering.

Copy Code

with ADODataSet1 do begin
  Filtered := False;
  Filter := 'State = ' + QuotedStr('CA') + ' OR ' +
    'State = ' + QuotedStr('CA');
  Filtered := True;
end;

Copy Code

ADODataSet1->Filtered = false;
ADODataSet1->Filter = "State = " + QuotedStr("CA") + " OR " + "State = " + QuotedStr("CA");
ADODataSet1->Filtered = true;

When a filter is set, Blank records do not appear unless explicitly included in the filter. To include rows based on blank, (or "NULL") values in table columns, compare the column to NULL in the expression. For example:

Copy Code

with ADODataSet1 do begin
  Filtered := False;
  Filter := 'State = ' + QuotedStr('CA') + ' OR ' +
    'State = NULL';
  Filtered := True;
end;

Copy Code

ADODataSet1->Filtered = false;
ADODataSet1->Filter = "State = " + QuotedStr("CA") + " OR " + "State = NULL";
ADODataSet1->Filtered = true;

When a field name contains spaces, you must enclose the field name in brackets. For example:

Copy Code

[Home State] = 'CA' or [Home State] = 'MA'

Filter expressions on remote SQL tables and on client datasets support field comparisons. For example:

Copy Code

Field1 > Field2

The FilterOptions property controls case sensitivity and filtering on partial comparisons.

Tip: Applications can set Filter at runtime to change the filtering condition for a dataset at (for example, in response to user input).

Note: : include a space between comparison values and comparison operators in filter expressions. For instance, ensure that there is a space after the field name and before the operator.