Search several fields

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.
sergio_2001Asked:
Who is Participating?
 
JaccoConnect With a Mentor Commented:
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
 
sergio_2001Author Commented:
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
 
JaccoCommented:
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
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.