Solved

Filtering Tables..

Posted on 2001-07-04
25
319 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

A lot of questions regard threads in Delphi.   One of the more specific questions is how to show progress of the thread.   Updating a progressbar from inside a thread is a mistake. A solution to this would be to send a synchronized message to the…
The uses clause is one of those things that just tends to grow and grow. Most of the time this is in the main form, as it's from this form that all others are called. If you have a big application (including many forms), the uses clause in the in…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

726 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