Using LIKE statement for filtering

Question

Im using this code to filter my table:

Table.Filtered := False;
Table.Filter := '[' + Field_Search + '] LIKE ''%' + Edit_Search.Text + '%''';
Table.Filtered := True;

but it raises this exception:

"Operation not applicable."

where is problem?

Answer 1

A TTable.Filter isn't a SQL query. LIKE isn't supported (neither is IN). The supported operators are =, <>, >, <, >=, '<=,AND,NOTandOR`, according to the documentation

For more complicated filtering, use the TDataSet.OnFilterRecord event:

procedure TForm1.Table1FilterRecord(Dataset: TDataset; var Accept: Boolean);
begin
  // Don't remember if D7 supports DataSet[FieldName] syntax; if not,
  // use DataSet.FieldByName instead, or a persistent field.
  Accept := Pos(Edit_Search.Text, DataSet[SearchField].AsString) > 0;
end;

Answer 2

Table.Filtered := False;
Table.Filter := Field_Search + ' LIKE ' + QuotedStr('*' + Edit_Search.Text + '*');
Table.Filtered := True;

Answer 3

you should use this :

   DataModule.Table.Filtered := False;
   DataModule.Table.Filter := 'Field_Name' + ' LIKE ' + QuotedStr(Edt_SearchByCode.Text +'%');
   DataModule.Table.Filtered := True;

and will work like a Magic and no use of TQuery any more .... and if you want Matching does not take case-sensitivity into account. you should use this code instead:

   DataModule.Table.Filtered := False;
   DataModule.Table.FilterOptions := [foCaseInsensitive];
   DataModule.Table.Filter := 'Field_Name' + ' LIKE ' + QuotedStr(Edt_SearchByCode.Text +'%');
   DataModule.Table.Filtered := True;