Setting the Filter Property

Go Up to Creating Filters

To create a filter using the Filter property, set the value of the property to a string that contains the filter's test condition. For example, the following statement creates a filter that tests a dataset's State field to see if it contains a value for the state of California:

Dataset1.Filter := 'State = ' + QuotedStr('CA');

Dataset1->Filter = "State = 'CA'";

You can also supply a value for Filter based on text supplied by the user. For example, the following statement assigns the text in from edit box to Filter:

Dataset1.Filter := Edit1.Text;

Dataset1->Filter = Edit1->Text;

You can, of course, create a string based on both hard-coded text and user-supplied data:

Dataset1.Filter := 'State = ' + QuotedStr(Edit1.Text);

Dataset1->Filter = AnsiString("State = '") + Edit1->Text + "'";

Blank field values do not appear unless they are explicitly included in the filter:

Dataset1.Filter := 'State <> CA or State = BLANK';

Dataset1->Filter = "State <> 'CA' or State = BLANK";

Note: After you specify a value for Filter, to apply the filter to the dataset, set the Filtered property to True.

Filters can compare field values to literals and to constants using the following comparison and logical operators:

Comparison and logical operators that can appear in a filter :

Operator	Meaning
<	Less than
>	Greater than
>=	Greater than or equal to
<=	Less than or equal to
=	Equal to
<>	Not equal to
AND	Tests two statements are both True
NOT	Tests that the following statement is not True
OR	Tests that at least one of two statements is True
+	Adds numbers, concatenates strings, adds numbers to date/time values (only available for some drivers)
-	Subtracts numbers, subtracts dates, or subtracts a number from a date (only available for some drivers)
*	Multiplies two numbers (only available for some drivers)
/	Divides two numbers (only available for some drivers)
*	wildcard for partial comparisons (FilterOptions must include foPartialCompare)

By using combinations of these operators, you can create fairly sophisticated filters. For example, the following statement checks to make sure that two test conditions are met before accepting a record for display:

(Custno > 1400) AND (Custno < 1500);

Note: When filtering is on, user edits to a record may mean that the record no longer meets a filter's test conditions. The next time the record is retrieved from the dataset, it may therefore "disappear." If that happens, the next record that passes the filter condition becomes the current record.

See Also

• Writing an OnFilterRecord Event Handler

Samples

• FireDAC TFDQuery Filter sample