We help IT Professionals succeed at work.

Database filtering...

harisis asked
Medium Priority
Last Modified: 2010-08-05
I want to filter a database acording to some cretiria.
For example i have a database with the following fields:


 I want to show every record that:
   a)the firstname begins with 'A'
   b)the secontname begins with 'B'
   c)the code is greater than '500'

 I tried to "setrangestart" and "setrangeend" but it doesn' work.
Watch Question

With Paradox or dBASE tables, these methods work only with indexed fields. With SQL databases, they can work with any columns specified in the IndexFieldNames property.

Provide some code and additional information if it is not the problem, such:

1) What indexes do you have in this table;

2) Did you try the Filter property;

3) Are you using TTable or TQuery;

4) What database (Paradox, Interbase, ...);



I use a TTable component with a Paradox table and i have created a secondary index named "secind" with all the field of the table in the above order.
 I have also put key in every field .

 I use the code below:

  With table1 do

 What the filter property has to do with my problem?

The correct usage of SetRangeStart and SetRangeEnd must be:

With Table1 do begin
    IndexName := 'secind';
    SetRangeStart; //Starts setting the starting values
    FieldByName('FirstName').asstring := 'A';
    SetRangeEnd; //Starts setting the ending values
    FieldByName('FirstName').asstring := 'A' + #255; // All A's

The filter has everything to do with your problem! Try for example:

With Table1 do begin
    Filter := 'Code>500';
    Filtered := True;

This will solve part of your problem. You'll still have some troubles cause when using composite indexes, it seems that you must provide exactly the first part of it to get the second range, for example:

With OtherTable do begin
    IndexName := 'DeptSal';
    SetRangeStart; //Starts setting the starting values
    FieldByName('Department').asstring := 'XPTO';
    FieldByName('Salary').asinteger := 1000;
    SetRangeEnd; //Starts setting the ending values
    FieldByName('Department').asstring := 'XPTO';
    FieldByName('Salary').asinteger := 5000;

So, AFAIK (As far as I Know), it's better using TQuery to get the better results in your case. It will take the advantages of the index in a simple way, like:

FROM MyTable
WHERE FirstName LIKE 'A%' AND SecondName LIKE 'B%' AND Code > 500

I know it's a workaround, that's why it's a comment not an answer (the first one was supposed to be the same - Sorry). I also have some hope that other experts can provide a solution to use Ranges with composite indexes. Otherwise, i'd like to submit this comment as an answer since we can only give answers, but they are  not always solutions.

----[S.M.I - Sori mi inglige] ---------

Just use the table's filter property.

codevalue := 500;
fnamevalue := 'A';
lnamevalue := 'B';

table1.filtered := false;
table1.filter := 'table1firstname.Value >= ' + '''' + fnamevalue + '''' +
' and table1lastname.Value < ' + '''' + lnamevalue + '''' +
' and table1code.Value = ' + '''' + IntToStr(codevalue) + '''';
table1.filtered := true;

Since the table is now filtered, you can only access those records that meet the criteria.  If you use the table's RecordCount function (table1.RecordCount) the returned value will be the number of records that meet this critiria as well.
Oops, I mis-read what you wanted to filter on.  The code should be as shown below with some additional explanation at the end.

codevalue := 500;
fnamebegin := 'A'; fnameend := 'B';
lnamebegin := 'B'; fnameend := 'C';

table1.filtered := false;
table1.filter :=
'firstname >= ' + '''' + fnamebegin + '''' +
'firstname < ' + '''' + fnameend + '''' +
' and lastname >= ' + '''' + lnamebegin + '''' +
' and lastname < ' + '''' + lnameend + '''' +
' and code > ' + '''' + IntToStr(codevalue) + '''';
table1.filtered := true;

The problem that you raise is how to select all the names that begin with A for firstname and B for lastname.  There is no way to access just the first character directly from the record when filtering or setting the range.  So you have to declare the begin and end for what you want to select.  I have hard coded these variables, but they can be easily modified so that all a user has to do is select a value to fill the fnamebegin variable and the fnameend variable can be generated by what ever function you decide.

As i had put in my comment you can really use the filter property to get the results, but be very carefull !!!
Filter doesn't do magic ! If you have a large table, it will not be efficient ! The best solution is use TQuery or mixing the SetRange... methods with Filter property.
I can provide some example if you reject tecbuilder's answer ( indeed it's really an example over one of the options in my comment)
I know you are goint to be fair ;)
harisis, I suggest using the filter option first.  It's very simple, easy to use, and you don't have to worry about updating your tables since the filter works on the table itself.

The SetRange properties are no faster than a filter when used on a TTable.  It works exactly as a filter.  The only difference being that the SetRange requires you to use the table's key fields to limit what you can see and the filter doesn't.  Also, when you want to go find a record you have to use the FindKey or SetKey..GotoKey methods which require you to only use the keyed fields.  The filter can be set via any field whether it's keyed or not.  With a filter to find a record you use the Locate method and again what you look for doesn't require the fields to be keyed.

If the filter doesn't do the job that you need it to do, then go with a TQuery.  TQuery is powerful because it allows you to use SQL.  However, it's also a bit more difficult since it works on a subset of the original table.


  Sorry ! I use Delphi 1 (client/server) and it doesn't recognize the filter property for a TTable.( or am i doing something wrong here?)

  If i use TQuery can i have the same functionality as if i use a TTable?
A TQuery  can be accessed the same way as a TTable component. So this could be a solution.
You could also use the OnFilter event of a TTable. It's a event in which you can set a return value (accept) to include or exclude the record in your result set.

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts


 I will use a TQuery finally... So the points go to Wilfred (he is the lucky one) Thank you all folks...
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.


Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.