problem with searching by date range

Hello,

I have a form that allows a user to search by job number, date range, or query string. The job number and query string work just fine but the date range returns no results.

Here is the code I have on my query results page:

-----------------------------------------------------------------
<cfquery name="q_queryResults" datasource="PLOG">
SELECT *
FROM data_admin.Main_log
WHERE del_job = 0
AND 0=0

<CFIF #FORM.Job_Number# IS NOT "">
AND Job_Number LIKE ('%#trim(FORM.Job_Number)#%')
</CFIF>

<CFIF #FORM.startDate# IS NOT "">
AND Date_Completed BETWEEN #CreateODBCDate(form.startDate)# AND #CreateODBCDate(form.endDate)#
</CFIF>

<CFIF #FORM.querystring1# IS NOT "">
AND Refuge_Literal LIKE ('%#trim(FORM.querystring1)#%') OR Requestor LIKE ('%#trim(FORM.querystring1)#%') OR Requestor_Office LIKE ('%#trim(FORM.querystring1)#%') OR Project_Type LIKE ('%#trim(FORM.querystring1)#%') OR Map_Type LIKE ('%#trim(FORM.querystring1)#%') OR Rq_Description LIKE ('%#trim(FORM.querystring1)#%') OR Assigned_To LIKE ('%#trim(FORM.querystring1)#%')
</CFIF>

<CFIF #FORM.querystring2# IS NOT "">
#FORM.querytype# Refuge_Literal LIKE ('%#trim(FORM.querystring2)#%') OR Requestor LIKE ('%#trim(FORM.querystring2)#%') OR Requestor_Office LIKE ('%#trim(FORM.querystring2)#%') OR Project_Type LIKE ('%#trim(FORM.querystring2)#%') OR Map_Type LIKE ('%#trim(FORM.querystring2)#%') OR Rq_Description LIKE ('%#trim(FORM.querystring2)#%') OR Assigned_To LIKE ('%#trim(FORM.querystring2)#%')
</CFIF>

ORDER BY Date_Assigned DESC, Job_Number DESC

</cfquery>
-----------------------------------------------------------------

I'm thinking maybe it's some kind of syntax or format problem?

Thanks for any help,
Melissa

It looks ok to me...are you getting an error or just getting no results? Can you look at the SQL and verify that it is running the correct part of the WHERE clause? And also verify that there are records that meet ALL of the criteria in the database?

Hi kiteless,

No errors are returned, just no data either. Using any of the other search terms (job#, query strings) work great, just the date range is the problem. The data type for the date_completed field is smalldatetime if that makes any difference (why I used CreateODBCDate). As for meeting criteria I think it should work. If I enter only a date range and nothing else then it should return all records between the dates specified and it returns nothing. If I combine a date range with a query string then it ignores the date range so it seems it isn't reading it at all?

thanks,
melissa

Can you look at the SQL statement that is running and verify that it looks correct? If you take that SQL and paste it into your database SQL editor, does it run?

Sorry if this is a dumb question but isn't the SQL statement what I've posted above? That is the only SQL code I have on the page.



Melissa

Someone on the Macromedia forums suggested I change the if statement to read something like this:
-------------------------
<CFIF IsDate(#FORM.startDate#) AND IsDate(#FORM.endDate#)>
AND (Date_Completed >= '#CreateODBCDateTime(FORM.startDate)#' AND Date_Completed <= '#CreateODBCDateTime(FORM.endDate)#' )
<CFELSEIF IsDate(#FORM.startDate#)>
AND (Date_Completed >= '#CreateODBCDateTime(FORM.startDate)#' )
<CFELSEIF IsDate(#FORM.endDate#)>
AND (Date_Completed <= '#CreateODBCDateTime(FORM.endDate)#' )
</CFIF>
--------------------------
but now when I test it with, for example, start date of 02/10/2001 and end date 06/30/2004 I get an error message of

Error Executing Database Query.
[Macromedia][SQLServer JDBC Driver][SQLServer]Line 9: Incorrect syntax near '2001'.

Thanks for any suggestions!
Melissa

No, I mean the actual SQL code that is sent to the database, after CF has evaluated everything. You can see this in the debugging output. Or you can copy the cfquery, wrap it in a <cfsavecontent> block which captures the output between the tags as a variable. Then you should be able to output the variable to see the actual SQL statement.

Oh duh, right. Thanks for the tip - we figured it out! Problem was that instead of '0' the del_job field was defaulting to NULL so it was returning no records because the SQL statement wasn't satisfied.

Thanks again!