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 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#"')>
</cfif>

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

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 but was refered here for the MySql support)

 

[TonyGroves]

What format are your dates in? MySQL expects the form "yyyy-mm-dd".

 

[ChuckB85]

<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>


The TreatStartDate is entered into the database is the format of yyyy-mm-dd

Hope this helps with solving the problem.

The odd thing with all of this is that before the server crash, this code worked. After formatting, reinstalling CF MX and MySql, everything works but this little piece of code. I know that the above code for the dates works properly because I use it on all the reports. Just for some reason the condition won't execute properly.

When I use this code:

<cfset Condition = ("diseaseclass = 'P-Pos.' AND PatientCat = 'N' AND Sex = 'Female' AND PTResults LIKE '%+' AND TreatStartDate >= '#Date1#' AND TreatStartDate <= '#Date2#'")>

I get this error:

Error Executing Database Query.
Syntax error or access violation: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'P-Pos.' AND PatientCat = 'N' AND Sex = 'Female' AND PTResults LIKE '%+' ' at line 3

If I use:

<cfset Condition = ('diseaseclass = "P-Pos." AND PatientCat = "N" AND Sex = "Female" AND PTResults LIKE "%+" AND TreatStartDate >= "#Date1#" AND TreatStartDate <= "#Date2#"')>

with the double quotes, it does not produce any errors. It also does return any records. If I hard code the condition into the query it works as excepted.

Any thoughts?
Thanks,
-Chuck