Solved

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

Posted on 2003-11-28
3
382 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 75 total points
ID: 9839832
just use this then

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

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This is a guide to setting up a new WHM/cPanel Server to be used for web hosting accounts. It is intended for web hosting company administrators and dedicated server owners. For under $99 per month (considering normal rate of Big Data Cetnters like …
Meet the world's only “Transparent Cloud™” from Superb Internet Corporation. Now, you can experience firsthand a cloud platform that consistently outperforms Amazon Web Services (AWS), IBM’s Softlayer, and Microsoft’s Azure when it comes to CPU and …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

752 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