Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2003-11-28
3
Medium Priority
?
394 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 300 total points
ID: 9839832
just use this then

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

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

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…
What You Need to Know when Searching for a Webhost Provider
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

722 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