Try this: <cfquery datasource="datasource.mdb" name="search"> Select * from products where type = '#form.type#' <cfif len(finish1)>AND finish1 = '#form.finish1#'</cfif> <cfif len(finish2)>AND finish2 = '#form.finish2#'</cfif> <cfif len(finish3)>AND system = '#form.system#' </cfif> </cfquery> Just make sure that the default (selected) option in the drop down has a value of "" (<option value="" selected>(select one)</option>) ----Original Message Follows---- From: Chad Gray <[EMAIL PROTECTED]> Reply-To: [EMAIL PROTECTED] To: CF-Talk <[EMAIL PROTECTED]> Subject: Passing empty form var to SQL WHERE Date: Wed, 13 Jun 2001 12:45:45 -0400 <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
