Posted by John, DelphiLand Team on April 15, 2009
In Reply to Database Filter with quote characters? posted by Phil Birell on
April 13, 2009
: If I filter database BOOKS by writing something in the Filter property, I do this in
the Object Inspector with for example: GENRE=FICTION
: But how to this at runtime? In my code I tried
Books.Filter=('GENRE=FICTION') and I tried Books.Filter=(GENRE='FICTION') and Books.Filter=('GENRE="FICTION"')
but none of these work.
: Also, how can I set up a filter at runtime based on text that was entered in
a TEdit?
: Books.Filter=('GENRE=Edit1.Text') is wrong, but how to do it right?
The explanation below is for DataSet components such as TTable (connected to a local file such as a dBase file, Paradox,...) and TClientDataset (connected for example to an XML file).
First, a small correction: if GENRE is a string field, then entering GENRE = FICTION in property Filter in the Object Inspector doesn't work, it should be GENRE = 'FICTION' -- but probably
that's what you meant :)
Next for setting up a filter in source code: Delphi converts each pair of single quotes into one single quote. To filter the dataset BOOKS on field GENRE equal to FICTION, you use this in your code:
Books.Filter := 'GENRE = ''FICTION''';
To filter on the text of a control in your filter, it's easier if you use the function QuotedStr():
Books.Filter := 'GENRE = ' + QuotedStr(Edit1.Text);
QuotedStr returns the quoted version of a string: a single quote character (') is inserted at the beginning and at the end of the given string, and each single quote character in the string is replaced by two single quotes.
Some more filter tips:
1. The property Filtered determines if a filter is applied. When Filtered is False, the filter is ignored.
2. The property FilterOptions is a set of values for use when filtering string fields:
- If foCaseInsensitive is included in FilterOptions, the comparison is case-insensitive.
- If foNoPartialCompare is not included, an asterisk (*) in the Filter property is treated as a "wildcard"; otherwise, an asterisk is treated as a literal character. Attention: 'p*' is OK, but '*p' nor '*p*' does not work.
3. To navigate through a filtered dataset, you can use FindFirst, FindNext, FindPrior, and FindLast.
For example, Books.FindFirst navigates to the first record that fits the filter.
Follow Ups