Topic: filtering on multiple columns with the and operato


rickaclark    -- 12-12-2009 @ 8:37 AM
 
I have created a timesheet that keeps track of my billable hours. I have a weekly_range form that sets a range for the days to output, ie: startDate and endDate. The output form (weekly_hours.cfm) outputs all projects  and their daily time totals that fall between the startdate and endDate range.

Here is the script that works perfectly:

<cfquery name="getDay" datasource="#application.track#">
    SELECT time_id, worker, day, project_no, phase_initial, descript, timeTotal, start_time, end_time
    From timesheet
    WHERE day BETWEEN #CreateODBCDate(form.StartDate)# AND #CreateODBCDate(form.EndDate)#
</cfquery>


What I want to do is output the hours for a particular customer(project_no) Unfortunately, when I try this I get the following error:

[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1.

The error occurred in C:\ColdFusion8\wwwroot\CFIDE\RickClark09\forms\weekly_hours.cfm: line 7

5 :     SELECT time_id, worker, day, project_no, phase_initial, descript, timeTotal, start_time, end_time
6 :     From timesheet
7 :     WHERE (project_no = #project_no#) AND (day BETWEEN #CreateODBCDate(form.StartDate)# AND #CreateODBCDate(form.EndDate)#)
8 : </cfquery>


Here is the code I am trying, what am I doing wrong???


<cfquery name="getDay" datasource="#application.track#">
    SELECT time_id, worker, day, project_no, phase_initial, descript, timeTotal, start_time, end_time
    From timesheet
    WHERE (project_no = #project_no#) AND (day BETWEEN #CreateODBCDate(form.StartDate)# AND #CreateODBCDate(form.EndDate)#)
</cfquery>



cfSearchin    -- 12-12-2009 @ 11:06 AM
  Most likely project_no is a "text" column. Enclose #project_no# in single quotes, so Access knows it the value is a string, not an column name.

WHERE project_no = '#project_no#'
....

Though it is really best to use cfqueryparam.


rickaclark    -- 12-12-2009 @ 3:51 PM
  Thanks, that is what it was. I've been staring at this code too long.




EasyCFM.COM ColdFusion Forums : http://archive.easycfm.com/forums
Topic: http://archive.easycfm.com/forums/viewmessages.cfm?Forum=30&Topic=14629