Solved

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

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

Network it in WD Red

There's an industry-leading WD Red drive for every compatible NAS system to help fulfill your data storage needs. With drives up to 8TB, WD Red offers a wide array of solutions for customers looking to build the biggest, best-performing NAS storage solution.  

Join & Write a Comment

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…
When it comes to showing a 404 error page to your visitors, you do not want that generic page to show, and you especially do not want your hosting provider’s ad error page to show either. In this article, I will show you how to enable the custom 40…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

758 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

24 Experts available now in Live!

Get 1:1 Help Now