We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

Search several fields

sergio_2001
sergio_2001 asked
on
Medium Priority
254 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.
Comment
Watch Question

Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview

Author

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.

Commented:
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
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.