Solved

Search several fields

Posted on 1998-07-03
3
178 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
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Objective: - This article will help user in how to convert their numeric value become words. How to use 1. You can copy this code in your Unit as function 2. than you can perform your function by type this code The Code   (CODE) The Im…
Hello everybody This Article will show you how to validate number with TEdit control, What's the TEdit control? TEdit is a standard Windows edit control on a form, it allows to user to write, read and copy/paste single line of text. Usua…
This is a video describing the growing solar energy use in Utah. This is a topic that greatly interests me and so I decided to produce a video about it.
This is a video that shows how the OnPage alerts system integrates into ConnectWise, how a trigger is set, how a page is sent via the trigger, and how the SENT, DELIVERED, READ & REPLIED receipts get entered into the internal tab of the ConnectWise …

914 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

21 Experts available now in Live!

Get 1:1 Help Now