Database filtering...

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.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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, ...);

harisisAuthor Commented:
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] ---------

HTML5 and CSS3 Fundamentals

Build a website from the ground up by first learning the fundamentals of HTML5 and CSS3, the two popular programming languages used to present content online. HTML deals with fonts, colors, graphics, and hyperlinks, while CSS describes how HTML elements are to be displayed.

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.
harisisAuthor Commented:
  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.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
harisisAuthor Commented:
 I will use a TQuery finally... So the points go to Wilfred (he is the lucky one) Thank you all folks...
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.