Data.DB.TDataSet.Filter

• Up to Parent: TDataSet

Delphi

property Filter: string read FFilterText write SetFilterText;

C++

__property System::UnicodeString Filter = {read=FFilterText, write=SetFilterText};

Properties

Type	Visibility	Source	Unit	Parent
property	public	Data.DB.pas Data.DB.hpp	Data.DB	TDataSet

Description

Specifies the text of the current filter for a dataset.

Use Filter to specify a dataset filter. When filtering is applied to a dataset, only those records that meet a filter's conditions are available. Filter describes the filter condition.

For example:

• For IBX.IBTable.TIBTable to view only those records where the value in the Country field contains 'France' or 'Fiji':

Country = 'France' or Country = 'Fiji'

You can use standard SQL wildcards such as percent (%) and underscore (_) in the condition when you use the LIKE operator. The following filter condition retrieves all Countries beginning with 'F':

Country LIKE 'F%'

To view rows that have a NULL value in the Country column and Contact_Name is not NULL, use the IS operator:

Country is NULL and Contact_Name is not NULL

You can also use complex expressions in filter clauses. The following example retrieves rows containing Country values that use Francs as currency.

Country IN (SELECT Country from Country where Currency = '_Franc')

• For Data.DB.TDataSet, the following filter condition displays only those records where the State field is 'CA' or 'MA':

State = 'CA' or State = 'MA'

The following run-time example shows how to assign that filter expression to the Data.DB.TDataSet.Filter property and activate the filtering.

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

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. For example:

State <> 'CA' or State = NULL

For example:

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

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:

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

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

Field1 > Field2

Field comparisons are not supported against local tables (Paradox, dBASE, Access, FoxPro).

For a complete list of operators you can use in filter strings, see Setting the Filter Property.

Note: Filters are not supported on unidirectional datasets. If you set the Filter property of a unidirectional dataset, it raises an exception.

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

Tip: Applications can set Filter at run time to change the dataset's filtering condition (for example, in response to user input).

See Also

• Data.DB.TDataSet.Filtered
• Data.DB.TDataSet.FilterOptions
• Data.DB.TDataSet.OnFilterRecord
• Data.DB.TDataSet.Locate
• Data.DB.TDataSet.Lookup
• Creating Filters
• Navigating Records in a Filtered Dataset
• Setting the Filter Property
• Displaying and Editing a Subset of Data Using Filters

Samples

• FireDAC TFDQuery Filter sample