newbie question: case problem

From: E. Liepins <eliepins_at_nrcan.gc.ca>
Date: 16 Jan 2004 05:53:17 -0800
Message-ID: <c7fe600f.0401160553.13b7757e@posting.google.com>

I am working on a search function for a database using ColdFusion - the database is Oracle. The first attempt at the code worked until a user put in a given name without the capital, then no results were generated. In order to address this problem, I added lower to the code. Now I achieve no results at all. Any suggestions?  

  <cfloop index="i" list=#form.keyword# delimiters=" ">   select * from archive
  where lower(projectnum) like '%lower(#i#)%'

  or lower(title) like '%lower(#i#)%'
  or lower(keywords) like '%lower(#i#)%' 
  or lower(investigators) like '%lower(#i#)%'
  or lower(location) like '%lower(#i#)%'
  or lower(establishment) like '%lower(#i#)%'
  or lower(maps) like '%lower(#i#)%'
  or lower(complocation) like '%lower(#i#)%'
  or lower(reference) like '%lower(#i#)%'
  	<cfif isnumeric(i)>
  	UNION 
	select * from archive
	where boxnum = #i# 
	 </cfif>

  UNION
  </cfloop>
  select * from archive where 1=2   

</cfquery> Received on Fri Jan 16 2004 - 07:53:17 CST