Need help, dynamic query

[ChuckB85]

I'm running ColdFusion MX 6.1 and MySql 4.1. I had a server crash last week and now some of my code is not working. Before the crash the code was working properly but now I have a problem with a cfset. This seams to be the only thing not working and I'm having a problem figuring out why not (since it worked before).

<cfif URL.QuarterReport IS "Quarter 1">
<cfset Date1 = #CREATEODBCDATE(createdate(URL.YearReport,01,01))#>
<cfset Date2 = #CREATEODBCDATE(createdate(URL.YearReport,03,31))#>
<cfelseif URL.QuarterReport IS "Quarter 2">
<cfset Date1 = #CREATEODBCDATE(createdate(URL.YearReport,04,01))#>
<cfset Date2 = #CREATEODBCDATE(createdate(URL.YearReport,06,30))#>
<cfelseif URL.QuarterReport IS "Quarter 3">
<cfset Date1 = #CREATEODBCDATE(createdate(URL.YearReport,07,01))#>
<cfset Date2 = #CREATEODBCDATE(createdate(URL.YearReport,09,30))#>
<cfelseif URL.QuarterReport IS "Quarter 4">
<cfset Date1 = #CREATEODBCDATE(createdate(URL.YearReport,10,01))#>
<cfset Date2 = #CREATEODBCDATE(createdate(URL.YearReport,12,31))#>
</cfif>



<cfif isDefined('URL.OneM')>
<cfset FieldName = "diseaseclass, PatientCat, Sex, PTResults">
<cfset Condition = ('diseaseclass = "P-Pos." AND PatientCat = "N" AND Sex = "Male" AND PTResults LIKE "%+"
AND TreatStartDate >= "#Date1#" AND TreatStartDate <= "#Date2#"')>
<cfelseif isDefined('URL.OneF')>
<cfset FieldName = "diseaseclass, PatientCat, Sex, PTResults">
<cfset Condition = ('diseaseclass = "P-Pos." AND PatientCat = "N" AND Sex = "Female" AND PTResults LIKE "%+"
AND TreatStartDate >= "#Date1#" AND TreatStartDate <= "#Date2#"')>

.....and so on


<cfquery name="PatientList" datasource="DSN">
SELECT ID, DistTBNum, TreatStartDate, RegisterDate, Surname, #FieldName#
FROM #tblName#
WHERE #Condition#
ORDER BY Surname ASC
</cfquery>

I know MySql does not like double quotes, but that was the only way it work before the crash. Just got the server up and running this morning and suddenly this doesent work.

This code is for a report. The user selects from two drop down menus. One for quarter and one for year. These vars get passed to the report via form.yearreport. Once there the user can click a number. A pop-up window appears with details of the number. The pop-up gets it's vars via url.yearreport

The query is not returning any records. When I hard code #condition# it works properly. I just need someone to clarify the proper syntax to use in the above <cfset condition =

Thanks in advance,
-Chuck
(P.S. I posted this in the ColdFusion forum and they refered me to this site for the MySql support)

 

[SQLDenis]

Post your question in the ColdFusion forum (

http://www.tek-tips.com/threadminder.cfm?pid=232)

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[SQLDenis]

oops didn't see this part "(P.S. I posted this in the ColdFusion forum and they refered me to this site for the MySql support)"


Anyway this is the SQL server forum, try the MySQL forum (

http://www.tek-tips.com/threadminder.cfm?pid=436)

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[ChuckB85]

Thanks, sorry about that.
-Chuck

 

[ChuckB85]

The Coldfusion forum I posted in was easycfm.com, so i'll try the CF forum on this site.
Thanks again,
-Chuck