<html>
OK im making a search application where people can use pull down menus to
select what to search on.<br>
<br>
In my action template im making a CFQUERY like this:<br>
<br>
<cfquery datasource="datasource.mdb"
name="search"><br>
Select *<br>
from products<br>
where type = '#form.type#' AND finish1 = '#form.finish1#' AND
finish2 = '#form.finish2#' AND system = '#form.system#' <br>
</cfquery><br>
<br>
My problem is if the person only selects one drop down from the form page
and leaves the others blank then i get a SQL statement like this:<br>
<br>
<font face="Courier New, Courier"><b>search</b> (Records=0,
Time=47ms)<br>
SQL = <br>
Select *<br>
from products<br>
where <x-tab> </x-tab>type = 'laminate' AND finish1 = '' AND
finish2 = '' AND system = ''<br>
<br>
</font>So no records are found. BTW I would like to use AND's not
OR's so the search is narrowed, and not broadened.<br>
<br>
I have tried some <CFIF> statements to figure out if the user
selected a drop down, but then i run into SQL problems where i have ANDs
next to each other.<br>
<br>
<font face="Courier New, Courier"><b>search</b> (Records=0,
Time=47ms)<br>
SQL = <br>
Select *<br>
from products<br>
where <x-tab> </x-tab>type = 'laminate' AND AND AND
</font> <br>
<br>
Then i tried to put the AND SQL inside of the CFIFs, but then i run into
problems where if the first one is not selected or the last one is not
selected the AND's are all screwed up also.<br>
<br>
Any ideas how to make this SQL WHERE statement work for empty form
variables?<br>
<br>
Thanks in advanced!<br>
Chad</html>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
