Try WHERE 1 = 1 Ade
-----Original Message----- From: Les Mizzell [mailto:[EMAIL PROTECTED] Sent: 29 September 2005 15:42 To: CF-Talk Subject: Query with multiple criteria Here's a problem that I run into a lot and have never come up with a good answer for... When building a search form that allows to search multiple criteria in a table(s) - lets say "lname", "category", and "color" for this example - the user can fill in any one of these, or all of them. So, when building the query, I always have trouble figuring out what the first item in the WHERE statement is, because you don't know which field above might or might not be filled in. So, if they fill in just "category" and "color" - see the problem? What's "best practice" for this? <cfquery name="myQuery"> Select * From Table Where ??? <cfif IsDefined("form.lname")> and lname like '%#form.lname#%' </cfif> <cfif IsDefined("form.category")> and lname like '%#form.category#%' </cfif> <cfif IsDefined("form.color")> and lname like '%#form.color#%' </cfif> </cfquery> The form I'm working on have close to 30 possible fields, and spans several tables... ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:219626 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
