Mega Code Archive
A Standard Filter Function
<![CDATA[
Title: A Standard Filter Function
Question: filter useful data in dataset
Answer:
procedure filter(query:Tquery);
var
fil_item0,fil_item1,fil_item2,fil_item3,fil_item4,fil_item5:string;
filter_equ0,filter_equ1,filter_equ2,filter_equ3,filter_equ4,filter_equ5:string;
filter_value0,filter_value1,filter_value2,filter_value3,filter_value4,filter_value5:string;
filter_logic0,filter_logic1,filter_logic2,filter_logic3,filter_logic4,filter_logic5:string;
sql,table_name:string;
begin
fil_item0 :=GetEnglishInfo(trim(combobox1.Text)) ;
filter_equ0:=trim(combobox2.Text);
if (lowercase(filter_equ0) = lowercase('Like') )or
(lowercase(filter_equ0) = lowercase('not Like') )then
filter_value0:=#39+'%'+trim(edit1.Text)+'%'+#39
else
filter_value0:=trim(edit1.Text) ;
filter_logic0:=trim(combobox3.Text) ;
fil_item1 :=GetEnglishInfo(trim(combobox4.Text)) ;
filter_equ1:=trim(combobox5.Text) ;
if (lowercase(filter_equ1) = lowercase('Like') )or
(lowercase(filter_equ1) = lowercase('not Like') )then
filter_value1:=#39+'%'+trim(edit2.Text)+'%'+#39
else
filter_value1:=trim(edit2.Text) ;
filter_logic1:=trim(combobox6.Text);
fil_item2 :=GetEnglishInfo(trim(combobox7.Text)) ;
filter_equ2:=trim(combobox8.Text);
if (lowercase(filter_equ2) = lowercase('Like') )or
(lowercase(filter_equ2) = lowercase('not Like') )then
filter_value2:=#39+'%'+trim(edit3.Text)+'%'+#39
else
filter_value2:=trim(edit3.Text) ;
filter_logic2:=trim(combobox9.Text);
fil_item3 :=GetEnglishInfo(trim(combobox10.Text)) ;
filter_equ3:=trim(combobox11.Text) ;
if (lowercase(filter_equ3) = lowercase('Like') )or
(lowercase(filter_equ3) = lowercase('not Like') )then
filter_value3:=#39+'%'+trim(edit4.Text)+'%'+#39
else
filter_value3:=trim(edit4.Text) ;
filter_logic3:=trim(combobox12.Text) ;
fil_item4 :=GetEnglishInfo(trim(combobox13.Text)) ;
filter_equ4:=trim(combobox14.Text) ;
if (lowercase(filter_equ4) = lowercase('Like') )or
(lowercase(filter_equ4) = lowercase('not Like') )then
filter_value4:=#39+'%'+trim(edit5.Text)+'%'+#39
else
filter_value4:=trim(edit5.Text) ;
filter_logic4:=trim(combobox15.Text) ;
fil_item5 :=GetEnglishInfo(trim(combobox16.Text)) ;
filter_equ5:=trim(combobox17.Text) ;
if (lowercase(filter_equ5) = lowercase('Like') )or
(lowercase(filter_equ5) = lowercase('not Like') )then
filter_value5:=#39+'%'+trim(edit6.Text)+'%'+#39
else
filter_value5:=trim(edit6.Text) ;
filter_logic5:=trim(combobox18.Text) ;
table_name:=table1.TableName;
sql:='select * from '+table_name;
if fil_item5 '' then
sql:=sql + ' where ( '+ fil_item0 +' '+filter_equ0+' '+filter_value0 +' ) '+filter_logic0+' ( '+
fil_item1 +' '+filter_equ1+' '+filter_value1 +' ) '+filter_logic1+' ( '+
fil_item2 +' '+filter_equ2+' '+filter_value1 +' ) '+filter_logic2+' ( '+
fil_item3 +' '+filter_equ3+' '+filter_value3 +' ) '+filter_logic3+' ( '+
fil_item4 +' '+filter_equ4+' '+filter_value4 +' ) '+filter_logic4+' ( '+
fil_item5 +' '+filter_equ5+' '+filter_value5 +' ) '
else if fil_item4 '' then
sql:=sql + ' where ( '+ fil_item0 +' '+filter_equ0+' '+filter_value0 +' ) '+filter_logic0+' ( '+
fil_item1 +' '+filter_equ1+' '+filter_value1 +' ) '+filter_logic1+' ( '+
fil_item2 +' '+filter_equ2+' '+filter_value2 +' ) '+filter_logic2+' ( '+
fil_item3 +' '+filter_equ3+' '+filter_value3 +' ) '+filter_logic3+' ( '+
fil_item4 +' '+filter_equ4+' '+filter_value4 +' ) '
else if fil_item3 '' then
sql:=sql + ' where ( '+ fil_item0 +' '+filter_equ0+' '+filter_value0 +' ) '+filter_logic0+' ( '+
fil_item1 +' '+filter_equ1+' '+filter_value1 +' ) '+filter_logic1+' ( '+
fil_item2 +' '+filter_equ2+' '+filter_value2 +' ) '+filter_logic2+' ( '+
fil_item3 +' '+filter_equ3+' '+filter_value3 +' ) '
else if fil_item2 '' then
sql:=sql + ' where ( '+ fil_item0 +' '+filter_equ0+' '+filter_value0 +' ) '+filter_logic0+' ( '+
fil_item1 +' '+filter_equ1+' '+filter_value1 +' ) '+filter_logic1+' ( '+
fil_item2 +' '+filter_equ2+' '+filter_value2 +' ) '
else if fil_item1 '' then
sql:=sql + ' where ( ( '+ fil_item0 +' '+filter_equ0+' '+filter_value0 +' ) '+filter_logic0+' ( '+
fil_item1 +' '+filter_equ1+' '+filter_value1 +' ) )'
else if fil_item0 '' then
sql:=sql + ' where '+ fil_item0 +' '+filter_equ0+' '+filter_value0
else
sql:=sql;
clipboard.AsText:=sql;
// Sql statement is stored in clipboard;
query.close;
query.sql.text:=sql;
try
query.open
except
showmessagebox('Something error in your sql statement');
end;
]]>