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

 

[r937]

instead of this --

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

please show us what you get for this --

<cfoutput>
SELECT ID, DistTBNum, TreatStartDate, RegisterDate, Surname, #FieldName#
FROM #tblName#
WHERE #Condition#
ORDER BY Surname ASC
</cfoutput>



r937.com | rudy.ca

 

[ChuckB85]

I'll try this in just a little while.

The only problem with doing just the <cfoutput> is that on the form I have a <cfoutput query=> that displays the returned records.

-Chuck

 

[r937]

yeah, just comment that part out, i would just like to see the actual query itself

r937.com | rudy.ca