asked on

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.

ASKER CERTIFIED SOLUTION

Jacco

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

sergio_2001

ASKER

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.

Jacco

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