Solved

Filtering Tables..

Posted on 2001-07-04
25
315 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:mnasman
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
 
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:mnasman
ID: 6253647
ondertol, you post ur comment as answer every where, your comments not the best answer, post it as comment

0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Suggested Solutions

This article explains how to create forms/units independent of other forms/units object names in a delphi project. Have you ever created a form for user input in a Delphi project and then had the need to have that same form in a other Delphi proj…
Creating an auto free TStringList The TStringList is a basic and frequently used object in Delphi. On many occasions, you may want to create a temporary list, process some items in the list and be done with the list. In such cases, you have to…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

747 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now