The problem is with the way you are using cfif and cfelseif.
Each of the cfelseifs should be their own cfifs. When the formfunding is
processed, the processing of the cfif stops.
Here is the modified code:
<CFQUERY datasource="liv8" name="funding">
SELECT * FROM funding
WHERE 1=1
<cfif Len(Trim(form.orgname))>
AND UPPER(orgname) LIKE UPPER(<cfqueryparam cfsqltype="CF_SQL_VARCHAR"
value="%#form.orgname#%">)</cfif>
<cfif Len(Trim(form.funding))>
AND UPPER(funding) LIKE UPPER(<cfqueryparam cfsqltype="CF_SQL_VARCHAR"
value="%#form.funding#%">)</cfif>
<cfif Len(Trim(form.surname))>
AND UPPER(surname) LIKE UPPER(<cfqueryparam cfsqltype="CF_SQL_VARCHAR"
value="%#form.surname#%">)</cfif>
<cfif IsNumeric(form.commyearfrom) AND IsNumeric(form.commmonthfrom)
AND IsNumeric(form.commdayfrom) AND IsNumeric(form.commyearto) AND
IsNumeric(form.commmonthto) AND IsNumeric(form.commdayto)>
AND IssueDate BETWEEN
TO_DATE('#form.commyearfrom#-#form.commmonthfrom#-#form.commdayfrom#','YYYY-
MM-DD')
AND
TO_DATE('#form.commyearto#-#form.commmonthto#-#form.commdayto#','YYYY-MM-DD'
)
</cfif>
</CFQUERY>
Steve
-----Original Message-----
From: Ian Vaughan [mailto:[EMAIL PROTECTED]
Sent: Friday, October 10, 2003 9:59 AM
To: CF-Talk
Subject: CFIF Troubles!!!
Iam having some trouble with cfif statements
If I enter more than one value in the search form such as
Surname = Vaughan
Funding = budget
The results bring back all surname records where the funding = budget when
it should be bringing back only the surnames that contain Vaughan
Is it something to do with my CFIF statements?
<CFQUERY datasource="liv8" name="funding">
SELECT * FROM funding
WHERE 1=1
<cfif Len(Trim(form.orgname))>
AND UPPER(orgname) LIKE UPPER(<cfqueryparam cfsqltype="CF_SQL_VARCHAR"
value="%#form.orgname#%">)
<cfelseif Len(Trim(form.funding))>
AND UPPER(funding) LIKE UPPER(<cfqueryparam cfsqltype="CF_SQL_VARCHAR"
value="%#form.funding#%">)
<cfelseif Len(Trim(form.surname))>
AND UPPER(surname) LIKE UPPER(<cfqueryparam cfsqltype="CF_SQL_VARCHAR"
value="%#form.surname#%">)
<cfelseif IsNumeric(form.commyearfrom) AND IsNumeric(form.commmonthfrom)
AND IsNumeric(form.commdayfrom) AND IsNumeric(form.commyearto) AND
IsNumeric(form.commmonthto) AND IsNumeric(form.commdayto)>
AND IssueDate BETWEEN
TO_DATE('#form.commyearfrom#-#form.commmonthfrom#-#form.commdayfrom#','YYYY-
MM-DD')
AND
TO_DATE('#form.commyearto#-#form.commmonthto#-#form.commdayto#','YYYY-MM-DD'
)
</cfif>
</CFQUERY>
----------------------
This is the output from debugging
Queries
funding (Records=8, Time=58ms)
SQL =
SELECT * FROM funding
WHERE 1=1
AND UPPER(funding) LIKE UPPER(:1)
Query Parameter Value(s) -
Parameter #1 = %Budget%
As you can see the surname field parameter does not seem to have been parsed
???
Any ideas?
Ian
_____
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
