Solved

Filtering Tables..

Posted on 2001-07-04
25
317 Views
Last Modified: 2010-04-06
How can i filter two or more fields TTable. I have 3 fields on my database which are named : date1, date2 and date3. I want to filter my Table just to show this dates.. Like this...

Table1.Filter :=  'DATE1 >= '''+DateToStr(Date)+''' AND DATE1< '''+DateToStr(DateTimePicker1.Date + 1)+'''';

Table1.Filtered := True;

???
0
Comment
Question by:systemop
  • 7
  • 5
  • 4
  • +4
25 Comments
 
LVL 9

Expert Comment

by:ITugay
ID: 6252493
Hi systemop,

just add more "AND" operators
something like this

  (Field1 > value1 and Field1 < value2) and (Field2 > value1 and Field2 < value2)

-----
Igor.
0
 
LVL 22

Expert Comment

by:Mohammed Nasman
ID: 6252504
Hello

  if you are use the query componet with parameters it will be more easier and felixible for u

  Query1.Close;
  Query1.SQL.text := 'select * from test where date1 between :d1 and :d2 ';
  Query1.ParamByName('d1').asdate := date;
  Query1.ParamByName('d2').asDate := DateTimePicker1.date;
  Query1.Open;

and you can add now more paramters and fields as you want to search

Best regards
Mohammed Nasman
0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 6252548
igor,

should it not be

(Field1 > value1 and Field1 < value2) or (Field2 > value1 and Field2 < value2)

?
0
ScreenConnect 6.0 Free Trial

Discover new time-saving features in one game-changing release, ScreenConnect 6.0, based on partner feedback. New features include a redesigned UI, app configurations and chat acknowledgement to improve customer engagement!

 
LVL 9

Expert Comment

by:ITugay
ID: 6252554
hi agian,

if you don't like to count commas, you can make your life much easy :-)

var
  S, D1, D2: String;
begin
  D1 := ''''+DateToStr(Date)+'''';
  D2 := ''''+DateToStr(Date+7)+'''';

  S := 'date1 >= :d1 and date1< :d2 and '+
       'date2 >= :d1 and date2 < :d2 and '+
       'date3 >= :d1 and date3 < :d2';

  S := StringReplace(S, ':d1', D1, [rfReplaceAll]); // replace all ":d1" to first date "D1"
  S := StringReplace(S, ':d2', D2, [rfReplaceAll]); // replace all ":d2" to first date "D2"

  Table1.Filter := S;

----
Igor.
0
 
LVL 9

Expert Comment

by:ITugay
ID: 6252561
meikl,
may be, he doesn't tell us about "AND" or "OR" condition. It was just my guess that it should be "AND" :-)
0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 6252613
my thought was, by so much ANDs the result may nothing,
but sure it depends on that, what systemop needs

meikl ;-)
0
 
LVL 2

Expert Comment

by:Drareg
ID: 6252708

You can use an other option by using the OnFilterRecord event.  Just set the Filter property to empty, and Filtered option to True.
In the procedure, do something like
   If <CONDITION> Then Accept := True ;

procedure TForm1.Table1FilterRecord(DataSet: TDataSet;
  var Accept: Boolean);
begin
  Accept := 'DATE1 >= '''+DateToStr(Date)+''' AND DATE1< '''+DateToStr(DateTimePicker1.Date +
1)+'''';
end;
0
 

Author Comment

by:systemop
ID: 6252881
I have a condition like this

Table1.Filter :=  'DATE1 >= '''+DateToStr(Date)+''' AND DATE1< '''+DateToStr(DateTimePicker1.Date +
1)+'''';

Table1.Filtered := True;

I want to filter DATEx fields between DateToStr(Date) and DateToStr(DateTimePicker1.Date + 1) so this must be so.
 

Table1.Filter :=  'DATE1 >= '''+DateToStr(Date)+''' AND DATE1 < '''+DateToStr(DateTimePicker1.Date +
1)+'''';
Table1.Filter :=  'DATE2 >= '''+DateToStr(Date)+''' AND DATE2 < '''+DateToStr(DateTimePicker1.Date +
1)+'''';
Table1.Filtered := True;

(Of Course DELPHI won't accept this but it is an idea of what i want to do...)
0
 
LVL 3

Expert Comment

by:VSF
ID: 6253012
If u have the RX Lib (www.rxlib.com) installed you can use their DateUtil.pas unit to increment the date.
Or you can use Delphi 6 DateUtil.pas if u already have D6.

Use the function
incdate(Date: Tdatetime,Days: integer,Months: integer,Years: integer): Tdatetime;


Declare DateUtil in your USES clause
And add the following code.

Table1.filtered:=false;  //<<Don't forget this!
Table1.Filter := '(DATE1 >= '''+DateToStr(Date)+''') AND (DATE1 < '''+DateToStr(incdate(DateTimePicker1.Date,1,0,0)+''')';
Table1.filtered:=true;



Hope this helps!
VSF
www.victory.hpg.com.br
UIN:14016999
0
 

Accepted Solution

by:
ondertol earned 40 total points
ID: 6253019
Write this code:

Table1.Filter :='((Date1 >= ''' +DateToStr(Date)+ ''')
and (Date1 < ''' +DateToStr(DateTimePicker1.Date+1)+'''))  
or (Date2 = ''' +DateToStr(Date)+ ''')';
Table1.Filtered := True;

I have add one "or" for complete the exercice!
Note the use one or two "(" and ")" !!!!  

bye bye
0
 
LVL 3

Expert Comment

by:VSF
ID: 6253094
ondertol:
Do you really think this code you wrote will work?!?!

Does your Delphi support this code????
DateToStr(DateTimePicker1.Date+1)

Mine doesn't!


0
 
LVL 22

Expert Comment

by:Mohammed Nasman
ID: 6253647
ondertol, you post ur comment as answer every where, your comments not the best answer, post it as comment

0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 6253698
ondertol,

thats not a fair bahaviour,
look at me i never post an answer,
since the questioner can choose,
which comment matches best its need,
and i got also gradings

there is no need to answer a question, specially,
if other experts giving correct answers in form as a comment

meikl ;-)
0
 
LVL 3

Expert Comment

by:VSF
ID: 6253707
"Datetimepicker1.date + 1"

He must be kiding!
0
 

Expert Comment

by:ondertol
ID: 6253785
sorry, i must use the comment!!!!! :(
0
 

Author Comment

by:systemop
ID: 6254592
Your code is not working ondertool but i did what i want as going your way 'OR' thanks..
0
 

Author Comment

by:systemop
ID: 6254593
Your code is not working ondertool but i did what i want as going your way 'OR' thanks..
0
 

Author Comment

by:systemop
ID: 6254601
Your code is not working ondertool but i did what i want as going your way 'OR' thanks..
0
 

Expert Comment

by:ondertol
ID: 6254607
VSF,

DateTimePicker1.Date + 1 is a valid istruction! Is used for add 1 day to the date. The output of DateTimePicker1.Date is one Float and the int part is one number who indicate the number of day after 12/30/1899


bye bye


I'm sorry for my use of answer.... but i'm begginner of this www! sorry sorry
0
 

Author Comment

by:systemop
ID: 6254612
Your code is not working ondertool but i did what i want as going your way 'OR' thanks..
0
 

Expert Comment

by:ondertol
ID: 6254621
systemop, you have write the filter code in only one line?
0
 

Expert Comment

by:ondertol
ID: 6254626
systemop, you have write the filter code in only one line?
0
 

Author Comment

by:systemop
ID: 6254635
Your code is not working ondertool but i did what i want as going your way 'OR' thanks..
0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 6254933
did you tried the other given suggestions, systemop?

(because not only the answerer can give working answers,
in this case a seeming not working answer)
0
 

Author Comment

by:systemop
ID: 6256200
First of all sorry for a lot of posts my explorer is crashed. I tried all the solutions of course because i must do what i want and ondertools answer is enough i think..
0

Featured Post

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction Raise your hands if you were as upset with FireMonkey as I was when I discovered that there was no TListview.  I use TListView in almost all of my applications I've written, and I was not going to compromise by resorting to TStringGrid…
In my programming career I have only very rarely run into situations where operator overloading would be of any use in my work.  Normally those situations involved math with either overly large numbers (hundreds of thousands of digits or accuracy re…
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question