EasyCFM.COM ColdFusion Forums / MS Access Related Issues / filtering on multiple columns with the and operato

   Reply to Discussion | New Discussion << previous || next >> 
Posted By Discussion Topic: filtering on multiple columns with the and operato

book mark this topic Printer-friendly Version  send this discussion to a friend  new posts last

rickaclark
12-12-2009 @ 8:37 AM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Junior Member
Posts: 82
Joined: Dec 2005


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
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Senior Member
Posts: 608
Joined: Feb 2008

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
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Junior Member
Posts: 82
Joined: Dec 2005

Thanks, that is what it was. I've been staring at this code too long.




Website Designed and Developed by Pablo Varando.