[allowsmilie] => 1 [showsignature] => 0 [ipaddress] => [iconid] => 0 [visible] => 1 [attach] => 0 [infraction] => 0 [reportthreadid] => 0 [isusenetpost] => 1 [msgid] => [ref] => [htmlstate] => on_nl2br [postusername] => mxstu [ip] => webforumsuser@m [isdeleted] => 0 [usergroupid] => [membergroupids] => [displaygroupid] => [password] => [passworddate] => [email] => [styleid] => [parentemail] => [homepage] => [icq] => [aim] => [yahoo] => [msn] => [skype] => [showvbcode] => [showbirthday] => [usertitle] => [customtitle] => [joindate] => [daysprune] => [lastvisit] => [lastactivity] => [lastpost] => [lastpostid] => [posts] => [reputation] => [reputationlevelid] => [timezoneoffset] => [pmpopup] => [avatarid] => [avatarrevision] => [profilepicrevision] => [sigpicrevision] => [options] => [akvbghsfs_optionsfield] => [birthday] => [birthday_search] => [maxposts] => [startofweek] => [referrerid] => [languageid] => [emailstamp] => [threadedmode] => [autosubscribe] => [pmtotal] => [pmunread] => [salt] => [ipoints] => [infractions] => [warnings] => [infractiongroupids] => [infractiongroupid] => [adminoptions] => [profilevisits] => [friendcount] => [friendreqcount] => [vmunreadcount] => [vmmoderatedcount] => [socgroupinvitecount] => [socgroupreqcount] => [pcunreadcount] => [pcmoderatedcount] => [gmmoderatedcount] => [assetposthash] => [fbuserid] => [fbjoindate] => [fbname] => [logintype] => [fbaccesstoken] => [newrepcount] => [vbseo_likes_in] => [vbseo_likes_out] => [vbseo_likes_unread] => [temp] => [field1] => [field2] => [field3] => [field4] => [field5] => [subfolders] => [pmfolders] => [buddylist] => [ignorelist] => [signature] => [searchprefs] => [rank] => [icontitle] => [iconpath] => [avatarpath] => [hascustomavatar] => 0 [avatardateline] => [avwidth] => [avheight] => [edit_userid] => [edit_username] => [edit_dateline] => [edit_reason] => [hashistory] => [pagetext_html] => [hasimages] => [signatureparsed] => [sighasimages] => [sigpic] => [sigpicdateline] => [sigpicwidth] => [sigpicheight] => [postcount] => 8 [islastshown] => 1 [isfirstshown] => [attachments] => [allattachments] => ) --> Query - Datediff problem - Coldfusion Database Access

Query - Datediff problem - Coldfusion Database Access

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

  1. #1

    Default Query - Datediff problem

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

    Niles Runeberg Guest

  2. #2

    Default Re: Query - Datediff problem

    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.

    jdeline Guest

  3. #3

    Default Re: Query - Datediff problem

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

    HTH
    --
    Tim Carley
    [url]www.recfusion.com[/url]
    [email]infoNOSPAMINGrecfusion.com[/email]
    Mountain Lover Guest

  4. #4

    Default Re: Query - Datediff problem

    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 Guest

  5. #5

    Default Re: Query - Datediff problem

    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.

    Niles Runeberg Guest

  6. #6

    Default Re: Query - Datediff problem

    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]infoNOSPAMINGrecfusion.com[/email]
    Mountain Lover Guest

  7. #7

    Default Re: Query - Datediff problem

    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]infoNOSPAMINGrecfusion.com[/email]
    Mountain Lover Guest

  8. #8

    Default Re: Query - Datediff problem

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

    mxstu Guest

Similar Threads

  1. Datediff Problem
    By Tim::. in forum ASP Database
    Replies: 6
    Last Post: October 9th, 07:49 PM
  2. Query on Query and CF casting problem
    By obxlefty in forum Coldfusion - Advanced Techniques
    Replies: 0
    Last Post: May 18th, 12:44 PM
  3. DateDiff Problem with the Year 2000
    By Smiller in forum Coldfusion - Advanced Techniques
    Replies: 1
    Last Post: May 9th, 03:24 AM
  4. Replies: 4
    Last Post: September 19th, 01:46 PM
  5. Using DatePart and DateDiff together in a query
    By Patty in forum Microsoft SQL / MS SQL Server
    Replies: 1
    Last Post: June 30th, 07:08 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •