AdoDB filter on merged columns

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.

Solution

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.