AdoDB filter on merged columns

Question

So I have an AdoTable connected to database (mdb) and DataSource using it. This DataSource is used by DBGrid...

I tried to filter AdoTable based on user input. There are 3 important columns: name, surname and ID. I came up with something like this as a temporary solution:

AdoTable.filter:='surname like ' +
      QuotedStr('%'+edit1.text+'%')+' or name like ' +
      QuotedStr('%'+edit1.text+'%')+' or ID like ' +
      QuotedStr('%'+edit1.text+'%');
AdoTable.filtered:=true;

It does work but it doesn't do exactly what I would want it to do... (when searching for name AND surename it won't find anything as it looks in one column only). So later I modified my code into this:

AdoTable.filter:='surname & " " & name like ' +
      QuotedStr('%'+edit1.text+'%')+' or name & " " & surname like ' +
      QuotedStr('%'+edit1.text+'%')+' or ID like ' +
      QuotedStr('%'+edit1.text+'%');
AdoTable.filtered:=true;

Now this would do exacly what I want it to do, but it raises exception (EOleException: Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another). That quite suprises me as I thought that it should behave as where clause in sql command (and it works perfectly as a command).

I tried replacing '&' with '+'. I could split an input text, but I don't want to do that (it would work poorly if you would have names like Robin van Persie, Ahmad ibn Hanbal, etc..)

Alternatively I could rewrite whole program to use queries instead of tables but I don't really want to do that (that would also mean that I would be getting new recordSet EVERYTIME user would change edit1.text instead of just filtering).

Any ideas?

edit: so command that works looks like this

select * from person where surname & " " & name like '%John Smith%' or name & " " & surname like '%John Smith%' or ID like '%John Smith%'

filter looks like this (and it triggers an exception)

surname & " " & name like '%John Smith%' or name & " " & surname like '%John Smith%' or ID like '%John Smith%'

Note that there could be 'hn Smith' instead of 'John Smith' so it would find also 'Kahn Smithers' etc.

There are some unknown parts: What type is ID? Your code assumes that it is a string which sounds not correct, mostly ID is a number. Second: What is your database? When you use e.g. SQL Express '%' is correct, when you use Access you have to use '*' Can you show the resulting filter string? — Christine Ross, Jan 30 '17 at 11:31
ID is indeed string. It can be something like '123abc'. And I found on-line that Access uses '%' ('*' doesn't work I tried). Resulting filter would be ' surname & " " & name like ''%John Smith%'' or name & " " & surname like ''%John Smith%'' or ID like ''%John Smith%'' ' if we are searching for name and surname or ' surname & " " & name like ''%0123abcd%'' or name & " " & surname like ''%0123abcd%'' or ID like ''%0123abcd%'' ' if we are searching for ID — Radim Nyč, Jan 30 '17 at 11:46

MartynA · Accepted Answer · 2017-01-30T14:32:48.937

The code below works fine with an AdoTable which accesses the employee table in the Delphi dbdemos.mdb database. My AdoConnection is using the Microsoft Jet 4.0 OLE DB driver.

procedure TForm1.Button1Click(Sender: TObject);
var
  FilterExpr : String;
begin
  AdoTable1.Filtered := not AdoTable1.Filtered;
  if AdoTable1.Filtered then begin
    FilterExpr := 'FirstName like ' + QuotedStr('%' + Edit1.Text + '%') + ' or LastName like ' + QuotedStr('%' + Edit1.Text + '%');
    AdoTable1.Filter := FilterExpr;
  end;
end;

I think your mistake probably is using that Access-specific syntax you mentioned. You're accessing the table through the ADO layer, and that AFAIK expects the same syntax as you would use, e.g. for a Sql Server back-end.

From your comment, it seems as if you want to cover the case where the user type into your Edit1.Text a fragment of a first name followed by a space followed by a fragment or a surname. The following will do that:

procedure TForm1.Button1Click(Sender: TObject);
var
  FilterExpr : String;
  P : Integer;
  S1,
  S2 : String;
begin
  AdoTable1.Filtered := not AdoTable1.Filtered;
  if AdoTable1.Filtered then begin
    P := Pos(' ', Trim(Edit1.Text));
    if P > 0 then begin
      S1 := Copy(Trim(Edit1.Text), 1, P - 1);
      S2 := Copy(Trim(Edit1.Text), P + 1, MaxInt);
      FilterExpr := '(FirstName like ' + QuotedStr('%' + S1 + '%') + ')';
      FilterExpr := FilterExpr + ' or (LastName like ' + QuotedStr('%' + S2 + '%') + ')';
    end
    else
      FilterExpr := 'FirstName like ' + QuotedStr('%' + Edit1.Text + '%') + ' or LastName like ' + QuotedStr('%' + Edit1.Text + '%');
    AdoTable1.Filter := FilterExpr;
  end;
end;

Update: If you want to allow the user to enter something like

hn Smith

then you could use a FilterRecord event like this instead of the code above.

procedure TForm1.ADOTable1FilterRecord(DataSet: TDataSet; var Accept: Boolean);
var
  S : String;
begin
  S := LowerCase(DataSet.FieldByName('FirstName').AsString + ' ' + DataSet.FieldByName('LastName').AsString);
  Accept := Pos(LowerCase(Edit1.Text), S) > 0; 
end;

The conversion to LowerCase, obviously, is to disregard any capitalisation the user might have used.

Yes but this would find either name or surname... Right? I got that to work, but I need both :D. Either way I thank you for you answer but I think I already found a solution... — Radim Nyč, Jan 30 '17 at 12:49
@RadimNyč: Sorry, what do you mean by "I need both"? If you mean you want the filter to find records where both the firtname and lastname contain what's in Edit1.Text, just change the "or" in the Filter to "and". — MartynA, Jan 30 '17 at 12:52
No... I need edit1.text to be like 'John Smith'.. this would look for name 'John Smith' and/or surname 'John Smith' and it wouldn't find anything... — Radim Nyč, Jan 30 '17 at 13:04
Nice, updated version would work.. but. As I wrote in my original post, '**I could split an input text, but I don't want to do that (it would work poorly if you would have names like Robin van Persie, Ahmad ibn Hanbal, etc..)**' In given examples you can't clearly decide if it's 'Robin' 'van Persie' and 'Ahmad' 'Ibn Hanbal' or 'Robin van' 'Persie' and 'Ahmad Ibn' 'Hanbal' so you would have to split it on every ' ' and use only first and the last(?) ('Robin' 'Persie' and 'Ahmad' 'Hanbal') and I just don't like that.. Needles to say that input like 'van Persie Robin' would be really bad... — Radim Nyč, Jan 30 '17 at 14:10

score 1 · Answer 2 · edited May 23 '17 at 10:29

I found this: Using LIKE statement for filtering and used the accepted answer and it works just fine. (Couldn't find it sooner as question quite differs)

On table filter:

procedure TDataModule1.ADOTableFilterRecord(DataSet: TDataSet;
  var Accept: Boolean);
var
  nameSurname :string;
  surnameName :string;
begin
  nameSurname:= DataSet.FieldByName('name').AsString+' '+DataSet.FieldByName('surname').AsString;
  surnameName:= DataSet.FieldByName('surname').AsString+' '+DataSet.FieldByName('name').AsString;

  if assigned(MainForm) then
    Accept := (Pos(MainForm.edit1.Text, nameSurname) > 0)
  or (Pos(MainForm.edit1.Text, surnameName) > 0)
  or (Pos(MainForm.edit1.Text, DataSet.FieldByName('ID').AsString) > 0);
end;

on edit change:

procedure TMainForm.edit1Change(Sender: TObject);
begin
    DataModule1.AdoTable.Filtered:=false;
    if edit1.Text<>'' then
      DataModule1.AdoTable.Filtered:=True;
end;

Thank you for your time... I'll leave it here.. I think eventually someone could need it

AdoDB filter on merged columns

2 Answers2