Solved

Search several fields

Posted on 1998-07-03
3
184 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 10

Accepted Solution

by:
Jacco earned 100 total points
ID: 1357094
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
ID: 1357095
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
ID: 1357096
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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Reconfigure Delphi Install? 2 77
Unique identifier on a terminal server (rdp) 4 85
DBCtrlGrid, Delphi, Scroll 8 86
Firemonkey BASS_Init into a thread 17 84
Introduction The parallel port is a very commonly known port, it was widely used to connect a printer to the PC, if you look at the back of your computer, for those who don't have newer computers, there will be a port with 25 pins and a small print…
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…

739 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