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
----- Original Message -----
From: "Jann VanOver" <[EMAIL PROTECTED]>
To: "CF-Talk" <[EMAIL PROTECTED]>
Sent: Wednesday, February 26, 2003 5:48 AM
Subject: Re: Correct Syntax for this piece of SQL ??
> I think Lee said it too, you've got a basic logic flaw. Your query says
all
> three criteria must be met.
>
> Did you mean to say:
>
> select * from
> funding
> Where ( orgname LIKE '%#Form.orgname#%' )
> OR ( funding LIKE '%#Form.funding#%' )
> OR ( commapproval LIKE '%#Form.commapproval#%' )
> ORDER BY recordid
>
> You can use parenthesis to be MORE subtle, like if you want the second and
> third criteria to be required together:
>
> Where ( orgname LIKE '%#Form.orgname#%' )
> OR
> ( ( funding LIKE '%#Form.funding#%' ) AND ( commapproval LIKE
> '%#Form.commapproval#%' ) )
>
>
>
> On 2/25/03 6:48 AM, "Ian Vaughan" <[EMAIL PROTECTED]>
wrote:
>
> > 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
Signup for the Fusion Authority news alert and keep up with the latest news in
ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
