Filtering Tables..

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;

???
systemopAsked:
Who is Participating?
 
ondertolConnect With a Mentor Commented:
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
 
ITugayCommented:
Hi systemop,

just add more "AND" operators
something like this

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

-----
Igor.
0
 
Mohammed NasmanSoftware DeveloperCommented:
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
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
kretzschmarCommented:
igor,

should it not be

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

?
0
 
ITugayCommented:
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
 
ITugayCommented:
meikl,
may be, he doesn't tell us about "AND" or "OR" condition. It was just my guess that it should be "AND" :-)
0
 
kretzschmarCommented:
my thought was, by so much ANDs the result may nothing,
but sure it depends on that, what systemop needs

meikl ;-)
0
 
DraregCommented:

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
 
systemopAuthor Commented:
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
 
VSFCommented:
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
 
VSFCommented:
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
 
Mohammed NasmanSoftware DeveloperCommented:
ondertol, you post ur comment as answer every where, your comments not the best answer, post it as comment

0
 
kretzschmarCommented:
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
 
VSFCommented:
"Datetimepicker1.date + 1"

He must be kiding!
0
 
ondertolCommented:
sorry, i must use the comment!!!!! :(
0
 
systemopAuthor Commented:
Your code is not working ondertool but i did what i want as going your way 'OR' thanks..
0
 
systemopAuthor Commented:
Your code is not working ondertool but i did what i want as going your way 'OR' thanks..
0
 
systemopAuthor Commented:
Your code is not working ondertool but i did what i want as going your way 'OR' thanks..
0
 
ondertolCommented:
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
 
systemopAuthor Commented:
Your code is not working ondertool but i did what i want as going your way 'OR' thanks..
0
 
ondertolCommented:
systemop, you have write the filter code in only one line?
0
 
ondertolCommented:
systemop, you have write the filter code in only one line?
0
 
systemopAuthor Commented:
Your code is not working ondertool but i did what i want as going your way 'OR' thanks..
0
 
kretzschmarCommented:
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
 
systemopAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.