Coldfusion/sql server search form question

1

Howdy all,

I am stumbling my way through learning coldfusion by building a project log for our group at work. I am stuck on building a search page. My search page seems really simple. I have one textbox that allows users to enter a job number, and then two text boxes that allow users to enter a word to search ALL the fields in the big table by. Between the two search fields are two radio buttons (and/or) so the user can specify keyword 1 AND/OR keyword 2.

I should mention now that the query and queryResults pages I am building are adopted from a set given me by an acquaintance (who actually got his to work but is unavailable to me at the moment).

The query page has the following code:


<html>
<head>
<title>Query the Project Log</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>

<body>
<div align="center">
  <p><font size="4"><strong>Query the Project Log</strong></font><br>
    (all fields optional)
</p>
</div>
<table align="center" cellpadding=10>
<form action="queryResults.cfm" method="post">
<tr><td width="79">Job Number:</td>
	<td width="350" colspan="2"><input name="Job_Number" type="text" maxlength="10"></td></tr>
<tr><td>Query string:</td>
    <td colspan="2"><input name="querystring1" type="text" size="50" maxlength="50"></td></tr>
<tr><td colspan="3" align="center">
	<INPUT TYPE="radio" NAME="querytype" VALUE="and" checked>AND
    <input type="radio" name="querytype" value="or">OR
</td></tr>
<tr><td>Query string:</td>
    <td colspan="2"><input name="querystring2" type="text" size="50" maxlength="50"></td></tr>
<tr><td div align="center" colspan="2"><input type="submit" value="Search"></td></tr>
</form>
 </table>
</body>
</html>

And the queryResults page has the following code:


<cfquery name="q_queryResults" datasource="PLOG">
SELECT *
FROM data_admin.Main_log
WHERE del_job = 0
AND 0=0

    <CFIF #FORM.Job_Number# IS NOT "">
       AND Job_Number LIKE (#FORM.Job_Number#)
    </CFIF>
	
	<CFIF #FORM.querystring1# IS NOT "">
       AND ( upper(trim(Refuge_Literal)||' '||trim(Requestor)||' '||trim(Requestor_Office)||' '||trim(Project_Type)||' '||trim(Map_Type)||' '||trim(Rq_Description) ||' '||trim(Assigned_To)) LIKE upper('%#FORM.querystring1#%')
    </CFIF>
	
    <CFIF #FORM.querystring2# IS "" AND #FORM.querystring1# IS NOT "">
       )
    </CFIF>

    <CFIF #FORM.querystring2# IS NOT "">
       #FORM.querytype# upper(trim(Refuge_Literal)||' '||trim(Requestor)||' '||trim(Requestor_Office)||' '||trim(Project_Type)||' '||trim(Map_Type)||' '||trim(Rq_Description) ||' '||trim(Assigned_To)) LIKE upper('%#FORM.querystring2#%') )
    </CFIF>
	
ORDER BY Date_Assigned DESC, Job_Number DESC

</cfquery>

and then a table to display the data.

But it’s not working. If I put in a job number, or even a partial job number then I get the table to display but no data is returned (only the column headings show). If I put something into the text fields then I get an error message that says

[Macromedia][SQLServer JDBC Driver][SQLServer]‘trim’ is not a recognized function name.

Sorry to go on and on but I really need this working. Anyone have a clue what is wrong or maybe a better way to search all fields using more than one keyword?

any help GREATLY appreciated!

:hr: