Query - Datediff problem

[Niles Runeberg]

Hello all,

I am trying to generate a list of users that have not responded to a survey in
3 months, but I am running into problems with the query...

I am using Access and here is my query...

<cfquery name="get_people" datasource="#Application.dsn#">
SELECT *
FROM contact
WHERE survey_type = 2
AND #dateDiff("m", Now(), datelast_responded)# <= 3
ORDER BY lname ASC
</cfquery>


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

What am I doing wrong??

[jdeline]

Try pulling the DateDiff function out of the WHERE clause and assign it to a
variable. Use that variable in your WHERE. If the problem is in the DateDiff
function, the error will be thrown on the assignment.

[Mountain Lover]

WHERE survey_type = 2 AND DateDiff('m', Now(), datelast_responded) <= 3

HTH
--
Tim Carley
[url]www.recfusion.com[/url]
[email]info@NOSPAMINGrecfusion.com[/email]

[Niles Runeberg]

Originally posted by: jdeline
Try pulling the DateDiff function out of the WHERE clause and assign it to a
variable. Use that variable in your WHERE. If the problem is in the DateDiff
function, the error will be thrown on the assignment.

But that would require another query wouldn't it? The variable
datelast_responded is coming right from the database...

[Niles Runeberg]

That doesn't work either... it doesn't limit the dates properly, the output has
an entry from today in there which means the datediff did not work.

I can use a cfif later down the page to filter the results, but that is not
good code.

[Mountain Lover]

What?
A datediff <= 3 months ('m') is going to include today, especially since
you are using Now(). Why would you think it should not?


--
Tim Carley
[url]www.recfusion.com[/url]
[email]info@NOSPAMINGrecfusion.com[/email]

[Mountain Lover]

If you don't want today, then you will need to use days ('y') and maybe
even Now()-1 and then test for <= 90

HTH

--
Tim Carley
[url]www.recfusion.com[/url]
[email]info@NOSPAMINGrecfusion.com[/email]

[mxstu]

Niles,

Are you trying to retrieve a list of users that last responded to a survey
over three months ago? So if today is 08/19/2005, you want people that last
responded prior to 05/20/2005? Sssuming your date column contains only a date
and not a date and time (ex. '2005-05-19 13:57:46'), you try something like the
query below. You may also want to check for null dates, to find users that
never responded at all.



---- example
SELECT (SomeColumns)
FROM contact
WHERE survey_type = 2
AND datelast_responded <= #CreateODBCDate(dateAdd("m", -3, now()))#
ORDER BY lname ASC

<!--- the query above translates to
......
WHERE survey_type = 2 AND datelast_responded <= {d '2005-05-19'}
--->