Hi All,

I have what I think will be a simple question, but I’m not sure how to re-write the query or the code that runs the query. I am using Elevate DB with EWB. The query I currently have is:

SELECT * FROM Clients
WHERE (FirstName LIKE {FirstName=''})
 AND (Surname LIKE {Surname=''})
 AND (Drivers_License LIKE {Drivers_License=''})
 AND (Passport_No LIKE {Passport_No=''})
 AND (DateOfBirth >= DATE{startDOB='1900-1-1'})
 AND (DateOfBirth <= DATE{endDOB='2100-1-1'})

When I fill in any of the edits in the screen shot attached, the query works perfectly – but only if the record has something in all the fields mentioned in the query. If for instance, the drivers license is blank, the query does not pick up the record. The % wildcards in the following code, seem to pickup anything so long as it’s not blank.

So, should I:
1.   Change the query or
2.   Change the way I am filling the Params in the code below or
3.   Create the query dynamically (if this is possible).

The Search button contains the following code:

 with qCliSearch do begin
   Params.Clear;
   Params.Add('Surname=' + QuotedStr('%' + eSurname.Text + '%'));
   Params.Add('FirstName=' + QuotedStr('%' + eFirst.Text + '%'));
   Params.Add('Drivers_License=' + QuotedStr('%' + eDriversLic.Text + '%'));
   Params.Add('Passport_No=' + QuotedStr('%' + ePassport.Text + '%'));
   if eDOB.Text <> '' then begin
     sDate := ADateStr(eDOB.SelectedDate);  //formats and quotes the date as ‘2000-01-31’
     Params.Add('startDOB=' + sDate);
     Params.Add('endDOB=' + sDate);
   end;
   Database.LoadRows(qCliSearch);
 end;


In the attached screen shot example, there is a record in the ‘Clients’ table with a first name of Ian, but this record has a blank drivers license (it is blank and not null).

Thanks for the help,
Paul

Here is something that could work.....

If you have a FirstName to search for do this....

WHERE ((FirstName like '%Jo%') or ('FN' = 'X'))

Else if FirstName entry is left blank do this

WHERE ((FirstName like '%%') or ('FN' = 'FN'))




WHERE ((FirstName LIKE {FirstName=''}  or ('FN' = {FN=}) ))
                                                         
if eFirst.Text > ''
begin
 Params.Add('FirstName=' + QuotedStr('%' + eFirst.Text + '%'));
 Params.Add('FN=' + QuotedStr('X'));
end
else
begin
 Params.Add('FirstName=' + QuotedStr('%' + eFirst.Text + '%'));
 Params.Add('FN=' + QuotedStr('FN'));
end;

Just a thought... someone else may have a better solution...

Walter

Paul,

<< When I fill in any of the edits in the screen shot attached, the query works perfectly – but only if the record has something in all the fields mentioned in the query. If for instance, the drivers license is blank, the query does not pick up the record. The % wildcards in the following code, seem to pickup anything so long as it’s not blank. >>

This is a bug in ElevateDB - apparently it doesn't like the consecutive double wildcards when dealing with blank column values.

The workaround is to make sure to only pass a single wildcard value when trying to query on a blank value.

Tim Young
Elevate Software
www.elevatesoft.com

Hi Guys,

Thanks heaps for the info and ideas. I have it working now.

Cheers,
Paul