Solved

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

Posted on 2003-11-28
3
374 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

Suggested Solutions

A web service (http://en.wikipedia.org/wiki/Web_service) is a software related technology that facilitates machine-to-machine interaction over a network. This article helps beginners in creating and consuming a web service using the ColdFusion Ma…
Introduction This article explores the design of a cache system that can improve the performance of a web site or web application.  The assumption is that the web site has many more “read” operations than “write” operations (this is commonly the ca…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

837 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