Solved

<cfparam name="form.field" default=""> not producing desired results

Posted on 2003-11-28
3
366 Views
Last Modified: 2013-12-24
i'm creating a little search function here. it works perfectly when i use

<cfparam name="form.LastName" default="">
<cfparam name="form.FirstName" default="">

with

<cfquery name="summary" datasource="lacma">
SELECT LastName, FirstName, Suffix, City, Zip, Spec FROM Doctordat WHERE 1=1 and LastName
LIKE '%#trim(form.Lastname)#%' and FirstName
LIKE '%#trim(form.FirstName)#%'
 ORDER BY LastName ASC
</cfquery>

but when i add

<cfparam name="form.Zip" default=""> and

<cfquery name="summary" datasource="lacma">
SELECT LastName, FirstName, Suffix, City, Zip, Spec FROM Doctordat WHERE 1=1 and LastName
LIKE '%#trim(form.Lastname)#%' and FirstName
LIKE '%#trim(form.FirstName)#%' and Zip
LIKE '%#trim(form.Zip)#%'
 
 ORDER BY LastName ASC
</cfquery>

to the mix and leave all fields empty, my results do not include records where the Zip field in the db is Null.  So my search results only include records that have zip codes. i want to return all records (inc. the ones w/o zip codes.
0
Comment
Question by:phillystyle123
  • 2
3 Comments
 
LVL 12

Expert Comment

by:jyokum
ID: 9839373
you could probably just change it to

and (Zip LIKE '%#trim(form.Zip)#%' OR Zip IS NULL)
0
 

Author Comment

by:phillystyle123
ID: 9839768
jyokum thanks for fielding this question - i thought everyone on earth was gone for the weekend.

Looking at the ?I think you answered it completely -so, thanks and i'm giving you the pts- i just wanted to clarify what i'm actually trying to do here:

and (Zip LIKE '%#trim(form.Zip)#%' OR Zip IS NULL)

works -  my only prob. is that won't it give me both the "LIKE" results and AND the Zip is Null Results?  I want this if the field is blank but i don't want the null results if i fill in the Zip field.  with your code the way it stands if i search for a 91406 (zip code) i'll get 91406 results and any records that have a null zip code field.

so can i change it to:

<cfif form.Zip is "">(Zip LIKE '%#trim(form.Zip)#%' OR Zip IS NULL)<cfelse>Zip LIKE '%#trim(form.Zip)#%' </cfif>




0
 
LVL 12

Accepted Solution

by:
jyokum earned 75 total points
ID: 9839832
just use this then

<cfif len(trim(form.Zip))>
AND Zip LIKE '%#trim(form.Zip)#%'
</cfif>
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Article by: kevp75
Hey folks, 'bout time for me to come around with a little tip. Thanks to IIS 7.5 Extensions and Microsoft (well... really Windows 8, and IIS 8 I guess...), we can now prime our Application Pools, when IIS starts. Now, though it would be nice t…
One of the typical problems I have experienced is when you have to move a web server from one hosting site to another. You normally prepare all on the new host, transfer the site, change DNS and cross your fingers hoping all will be ok on new server…
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.

785 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