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
EvgenyK
(Evgeny Karpov)
October 11, 2017, 8:17am
2
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;
EvgenyK
(Evgeny Karpov)
October 11, 2017, 10:27am
4
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.
EvgenyK
(Evgeny Karpov)
October 12, 2017, 9:52am
7
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;
antonk
(antonk)
October 12, 2017, 10:15am
8
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
It works, the Code was correct ( your code of course also ). The Problem was:
Did try everything except that…
As always thank you for your great Support.
Shalom
Manfred