Database Filter with quote characters?

Question

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?

Answer

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.