ApplyFilter Problem

Hi i try to filter a sql Table with this filter:

this works:

DM.ApplyFilter(DM_reports.tbl_rep_mbp_h,‘(H_O_DATE IS NULL AND STAT_DEL = 0)’,‘H_O_DATE’);

i need also a date ( pere is the Problem:

DM.ApplyFilter(DM_reports.tbl_rep_mbp_h,'(H_O_DATE IS NULL OR H_O_DATE < '+'''1.1.1900'''+' AND STAT_DEL = 0)','H_O_DATE');

in phpmyadmin this works:

SELECT * FROM MBP_H WHERE C_NO LIKE ‘I47716’ AND (H_O_DATE < ‘1.1.1900’ OR H_O_DATE IS NULL) AND STAT_DEL = 0

Thanks for any help.
Shalom
Manfred

I use Lazarus 1.6.4
Relativity Server: v9.1.100.1267
Data Abstract: v9.1.100.1267
RemObjects SDK: v9.1.100.1267

Framework: v4.0.30319.42000
Operating System: Unix 3.13.0.42
Platform: Unix

can you show your ApplyFilter implementation?

of, course:

procedure TDM.ApplyFilter(aTable: TDAMemDataTable; aFilter, aSorting: String);
begin
  aTable.DisableControls;
  try
    aTable.IndexFieldNames := aSorting;
    aTable.Filter := aFilter;
    aTable.Filtered := aTable.Filter <> '';
  finally
    aTable.EnableControls;
  end;
end;

try to specify separator and format like

  DateSeparator := '.';
  ShortDateFormat := 'dd.mm.yyyy';
  DM.ApplyFilter(DM_reports.tbl_rep_mbp_h,'(H_O_DATE IS NULL OR H_O_DATE < '+'''1.1.1900'''+' AND STAT_DEL = 0)','H_O_DATE');

will it solve problem?

For me it look like the Date is completely ignored. tryed many ways…

Did some more testing:
This is also not working:

 DateSeparator := '.';
 ShortDateFormat := 'dd.mm.yyyy';
 sql_string_rep_default_d:=  'SELECT * FROM MBP_H '+
                                    'WHERE C_NO = ''' + camp_ident_part + ''' '+
                                    'AND (H_O_DATE < ''' +'1.1.1900'+''' '+
                                    'OR H_O_DATE IS NULL) '+
                                    'AND STAT_DEL = 0 '+
                                    'ORDER BY H_D_DATE DESC';

with DM.RemoteDataAdapter do
begin
    FillWithDASql(DM_reports.tbl_rep_mbp_h,sql_string_rep_default_d, nil);
end;   

Updatet also to the newest Beta without any changes.

it works for me as expected:

code:

procedure TForm75.Button1Click(Sender: TObject);
var
  i: integer;
begin
  DAMemDataTable1.Active := False;
  DAMemDataTable1.Open;
  for i:=0 to 20 do
  DAMemDataTable1.AddRecord(['fld'],[EncodeDate(1900+i*10, 1,1)]);
end;

procedure TForm75.Button2Click(Sender: TObject);
begin
  DAMemDataTable1.Filtered := not DAMemDataTable1.Filtered;
  DAMemDataTable1.Filter := Edit1.Text;
end;

How excactly it doesn’t work?

You have to either pass date value in format your DB will understand or (recommended approach) use parametrized DA SQL instead

Hi Antonk

:zipper_mouth_face:
It works, the Code was correct ( your code of course also ). The Problem was:
image

Did try everything except that…:scream:
As always thank you for your great Support.
Shalom
Manfred