If one of your form variables is empty, it will always return all the records because you match '%%' (which means anything). If you want to match one of the criteria you entered, this should work: SELECT * FROM funding WHERE 0=1 <cfif Len(Trim(form.orgname))> OR UPPER(orgname) LIKE UPPER(<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="%#form.orgname#%">) </cfif> <cfif Len(Trim(form.funding))> OR UPPER(funding) LIKE UPPER(<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="%#form.funding#%">) </cfif> <cfif Len(Trim(form.commapproval))> OR UPPER(commapproval) LIKE UPPER(<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="%#form.commapproval#%">) </cfif> This will have no result if you don't enter a value at all. If you want to match all if you don't provide any value, you can add a cfif around the entire where clause <cfif Len(Trim(form.orgname&form.funding&form.commapproval))>
-----Oorspronkelijk bericht----- Van: Ian Vaughan [mailto:[EMAIL PROTECTED] Verzonden: wo 26/02/2003 10:20 Aan: CF-Talk CC: Onderwerp: Re: Correct Syntax for this piece of SQL ?? Hi I have just tried the following query select * from funding Where ( orgname LIKE '%#Form.orgname#%' ) OR ( funding LIKE '%#Form.funding#%' ) OR ( commapproval LIKE '%#Form.commapproval#%' ) ORDER BY recordid however whatever I type in the fields it returns all results ??? All 3 criteria do no have to be met, Any ideas why the above sql is returning all results ?? Ian ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4