Solved

Search several fields

Posted on 1998-07-03
3
177 Views
Last Modified: 2010-04-04
Hi all,

I need search for fields in a record giving several data, for example:
I have fields A,B,C,D
- If I Inform just the field A (value) it searches
- If I inform fields B and C it searches too. ( note that I don't give fields A and D)
My problem is the blanks fields. It doesn't find the record if I don't give all field's value And I want give the datas I have.
Answers using locate or SQL will be wellcome.
I hope to be clear.

Thanks in advance.
0
Comment
Question by:sergio_2001
  • 2
3 Comments
 
LVL 10

Accepted Solution

by:
Jacco earned 100 total points
Comment Utility
Locate solution:

Table1.Locate('A',[123],[]);

Table1.Loacte('B;C',[123,'ABC'],[]);

SQL solution

SELECT
  *
FROM
  TABLE
WHERE
  A = 123

SELECT
  *
FROM
  TABLE
WHERE
  B = 123 AND
  C = 'ABC'

If you specify indexes on the table on A and BC (and possibly other values) you can also use the SetKey/FindNearest/FindKey method. Look it up in the help files.

Also when you define these indexes, the SQL and Locate searches will become faster because they will both use the available indexes in the search.

If you use four edits to search your table you should build your SQL statement dynamically:

  with Query1 do begin
    sExtra := '';
     Close;
     SQL.Clear;
     SQL.Add('SELECT * FROM TABLE WHERE ');
     if EditA.Text<>'' then sExtra := 'A = '+EditA.Text; // for a number this way
     if EditB.Text<>'' then begin
       if sExtra<>'' then sExtra:=sExtra + ' AND ';
       sExtra := sExtra + 'B = "'+EditB.Text+'"'; // for a string this way
     end;
     DateTimeToString(sDate,'dd/mm/yyyy',Now);
     if EditC.Text<>'' then begin
       if sExtra<>'' then sExtra := sExtra + ' AND ';
       sExtra := sExtra + 'C = "'+sDate+'"'; // for a date this way
     end;
     if EditD.Text<>'' then begin
       if sExtra<>'' then sExtra := sExtra + ' AND ';
       sExtra := sExtra + 'D = '+EditD.Text;
     end;
     SQL.Add(sExtra);
     Open;
  end;

Hope this helps if not please add a comment on how you want to implement this feature.

Regards jacco
0
 

Author Comment

by:sergio_2001
Comment Utility
Jacco,

Your dinamic SQL does what I need.
But there is no other way to do that? A easy one....
Using locate for example... The problem is when I want to use several fields, 10 for example. My code become one mess...
But your answer works.... Please send-me the answer.

Thanks.
0
 
LVL 10

Expert Comment

by:Jacco
Comment Utility
The locates should work as well.

Look in the help files.

In a locate you can specify more then one field by separating the fieldnames by semicolons. The values a passed to the function using a variant array. You can also make those dynamically:

A procedure could look like this:

procedure TForm1.Button1Click(Sender: TObject);
var
  VarArr : Variant;
  sDate,sKey : String;
  iCount : Integer;
begin
  VarArr:=VarArrayCreate([0,0],varVariant);
  iCount := 0;
  sKey := '';
  if Edit1.Text<>'' then begin
    sKey:=sKey + 'A';
    VarArr[iCount]:=StrToInt(Edit1.Text);
    Inc(iCount);
  end;
  if Edit2.Text<>'' then begin
    if sKey<>'' then sKey:=sKey+';';
    sKey:=sKey + 'B';
    VarArrayRedim(VarArr,iCount);
    VarArr[iCount]:=Edit2.Text;
    Inc(iCount);
  end;
  if Edit3.Text<>'' then begin
    if sKey<>'' then sKey:=sKey+';';
    sKey:=sKey+'C';
    DateTimeToString(sDate,'dd/mm/yyyy',StrToDate(Edit3.Text));
    VarArrayRedim(VarArr,iCount);
    VarArr[iCount]:=sDate;
  end;
  Table1.Locate(sKey,VarArr,[]);
end;

First choose do the locate using dynamic SQL or Locate then simplify the routines by separating it into separate functions.

In this example you could make:

procedure TForm1.Button1Click(Sender: TObject);
var
  VarArr : Variant;
  sDate,sKey : String;
  iCount : Integer;

  procedure AddToVarArr(V : Variant);
  begin
    VarArrayRedim(VarArr,iCount);
    VarArr[iCount]:=V;
    Inc(iCount);
  end;

  procedure AddToKey(const FieldName : String);
  begin
    if sKey<>'' then sKey:=sKey+';';
    sKey:=sKey + FieldName;
  end;

begin
  VarArr:=VarArrayCreate([0,0],varVariant);
  iCount := 0;
  sKey := '';
  if Edit1.Text<>'' then begin
    AddToKey('A');
    AddToVarArr(StrToInt(Edit1.Text));
  end;
  if Edit2.Text<>'' then begin
    AddToKey('B');
    AddToVarrArr(Edit2.Text);
  end;
  if Edit3.Text<>'' then begin
    DateTimeToString(sDate,'dd/mm/yyyy',StrToDate(Edit3.Text));
    AddToKey('C');
    AddToVarArr(sDate);
  end;
  Table1.Locate(sKey,VarArr,[]);
end;

To make it even more flexible you could try using a for lus to add X elements...

Using FindComponent('Edit'+IntToStr(iCount));

The converting of type you could store in EditX.Tag

1 = Integer
2 = String
3 = Date
etc

And you could make one procedure:

procedure AddToSearch(Edit : TEdit);
begin
  if Edit.Text<>'' then begin
    case Edit.Tag of
      1 : begin
      end;
      2 : begin
      end;
      3 : begin
      end;
    end;
  end;
end;

Good luck and regards Jacco
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

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…
Have you ever had your Delphi form/application just hanging while waiting for data to load? This is the article to read if you want to learn some things about adding threads for data loading in the background. First, I'll setup a general applica…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

743 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

12 Experts available now in Live!

Get 1:1 Help Now