Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » Elevate Web Builder Technical Support » Support Forums » Elevate Web Builder General » View Thread |
Messages 1 to 4 of 4 total |
Query problem returning records with blank values |
Thu, Oct 12 2017 12:26 AM | Permanent Link |
Paul Coshott | 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 Attachments: client_search.png |
Thu, Oct 12 2017 10:41 AM | Permanent Link |
Walter Matte Tactical Business Corporation | 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 |
Thu, Oct 12 2017 1:27 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
Fri, Oct 13 2017 4:58 AM | Permanent Link |
Paul Coshott | Hi Guys,
Thanks heaps for the info and ideas. I have it working now. Cheers, Paul |
This web page was last updated on Tuesday, September 17, 2024 at 04:19 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |