Multiple combo boxes to filter a database

Question

I want to be able to filter a database from what the user has selected in two different combo boxes.

When something is chosen from the combo box

var i : integer;
begin
i := cmbA.ItemIndex;
bA := True; // global boolean variable 
case i of
0 : strA := 'One'; //Global string variable
1 : strA := 'Two';
else bA:= False;
end;

This code is repeated in the second combo box except where there is an 'A' there is a 'B'.

Once the user clicks a button

if bA then
dmDatabase.tblTable.Filter := 'A = ' + QuotedStr(strA);
if bSize then
dmDatabase.tblTable.Filter := 'B = ' + QuotedStr(strB);

The problem with this code is that the one cancels the other one. For instance, if something is chosen from A and then from B only the B filter will show in the dbGrid.

What I want is for the filter to only show those in the database that apply to both 'A' and 'B'.

dmDatabase.tblTable.Filter := 'A = ' + QuotedStr(strA) + ' AND B = ' + QuotedStr(strB); or write a handler for the OnFilterRecord event (which is the way I would prefer in this case). — TLama

bummi bummi · Accepted Answer · 2014-10-22T22:28:17

The Filter propertyof a dataset is capable of evalaluating complex criterias.
You might e.g. have contitions like:

aField=1 and (bField Like 'Am%' or bField Like 'Bm%') AND cField is not NULL

Since you just want to add AND conditions with no requirement for compounding a simple produre declared in the public section of might be useful here:

Procedure dmDatabase.AddToFilter(const Expression: String);
Const
  C_AndIfNotEmpty: Array [boolean] of String = ('', ' AND ');
begin
  tblTable.Filter := tblTable.Filter + C_AndIfNotEmpty[TRIM(tblTable.Filter) <> '']
                   + Expression;
end;

With a fire and forget call like:

if bA then
  dmDatabase.AddToFilter('A = ' + QuotedStr(strA));
if bSize then
  dmDatabase.AddToFilter('B = ' + QuotedStr(strB));

Multiple combo boxes to filter a database

1 Answers