Just a stab in the dark:
Is orgname always uppercase in your DB? If so, that might explain why
searching by orgname always works but searching by the others doesn't.
You might have to do this:
...AND UPPER(funding) LIKE UPPER('%#Form.funding#%')
or EVEN (the same):
...AND UPPER(funding) LIKE '%#UCase(Form.funding)#%'
etc.
Thanks,
André
-----Original Message-----
From: Ian Vaughan [mailto:[EMAIL PROTECTED]
Sent: 25 February 2003 14:49
To: CF-Talk
Subject: Correct Syntax for this piece of SQL ??
Hi
I have the following form which is being used to search against my
database
table.
When I search using the 'orgname' field and there are matches then
results
are returned. However if I search in the other two fields then it
displays
no results found when it should bring back results ?
Is this because of my sql ??
<CFQUERY datasource="liv8" name="funding"
cachedwithin="#CreateTimeSpan(0,6,0,0)#" blockfactor="100">
select * from
funding
Where orgname LIKE UPPER('%#Form.orgname#%')
AND funding LIKE UPPER('%#Form.funding#%')
AND commapproval LIKE UPPER('%#Form.commapproval#%')
ORDER BY recordid
</CFQUERY>
This is my search form
<form method="POST" action="/testpages/fundingresults.cfm"
name="search">
<input type="Hidden" name="MaxRows" value="100">
<input type="hidden" name="StartRow" value="1">
<font color="#000000" face="Verdana"><span class="black10">
<p>
<input type="text" class=mini name="orgname" size=22 style="WIDTH:
200px"><b> Organization Name</b>
</p>
<input type="text" class=mini name="funding" size=22 style="WIDTH:
200px"><b>Source of Funding</b>
<p>
<input type="text" class=mini name="commapproval" size=22 style="WIDTH:
200px"><b>Committee Approval Date</b>
</p>
<p>
<input type="submit" value="Search" name="B1">
<input type="reset" value="Reset Form" name="B2">
<p>
</form>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
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
Your ad could be here. Monies from ads go to support these lists and provide more
resources for the community. http://www.fusionauthority.com/ads.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
