Professional Web Applications Themes

Help! What's wrong with my QUERY statement?? - Coldfusion Database Access

Also, checking the table, duedate is held as 8/12/2005 whereas if I #dateformat(duedate,"mm/dd/yyyy")# it will come out with a leading 0. Is that throwing me off? Here's my statement <cfquery datasource="HelpDesk" name = "reminder"> select a.entry_date, a.entry_description, a.emailto, a.remind, b.email from Help_Desk_List a, TechsTbl b where ((a.duedate = '#dateformat(now,"mm/dd/yyyy")#') and (a.status = 'Open') and (a.name = b.name) and (a.remind = 'Yes')) </cfquery>...

  1. #1

    Default Help! What's wrong with my QUERY statement??

    Also, checking the table, duedate is held as 8/12/2005 whereas if I
    #dateformat(duedate,"mm/dd/yyyy")# it will come out with a leading 0. Is that
    throwing me off?

    Here's my statement


    <cfquery datasource="HelpDesk" name = "reminder">
    select a.entry_date, a.entry_description, a.emailto, a.remind, b.email
    from Help_Desk_List a, TechsTbl b
    where ((a.duedate = '#dateformat(now,"mm/dd/yyyy")#') and (a.status =
    'Open') and (a.name = b.name) and (a.remind = 'Yes'))
    </cfquery>

    RuBot Guest

  2. #2

    Default Re: Help! What's wrong with my QUERY statement??

    <cfset datenow = "#dateformat(now(),"mm-dd-yyyy")#">
    <cfquery datasource="HelpDesk" name = "reminder">
    select a.entry_date, a.entry_description, a.emailto, a.remind, b.email
    from Help_Desk_List a, TechsTbl b
    where ((a.duedate = #datenow#)
    and (a.status = 'Open') and
    (a.name = b.name) and
    (a.remind = 'Yes'))
    </cfquery>

    this also produces no error, but no results as well.


    RuBot Guest

  3. #3

    Default Re: Help! What's wrong with my QUERY statement??

    I am going to guess its your 'and' statements, you are requireing that all of those exist when they may not therefore, no results. Try changing the 'and' to 'or' and see what you get then.
    rmorgan Guest

  4. #4

    Default Re: Help! What's wrong with my QUERY statement??

    What database do you have?
    Maybe it's case sencitive then values must be 'OPEN' and "YES". Also most likely #datenow# must be in single quotes:
    '#datenow#'.
    CF_Oracle Guest

  5. #5

    Default Re: Help! What's wrong with my QUERY statement??

    Try it without the DateFormat function. The function returns a string and the datatype in the database is most likely a date.
    san_diego_ca Guest

  6. #6

    Default Re: Help! What's wrong with my QUERY statement??

    RuBot,

    I don't think Access will interpret the date correctly if you pass it that
    way. Try using the CreateODBCDate() method intstead. It ensures that the date
    is passed to the database in a format it can interpret correctly. Assuming the
    table contains the data that match the WHERE clause conditions, the query below
    will return results.

    Also, if the "name" field is a "text" field (ex. 'JohnSmith'), you should
    think about redesigning the tables. It is a bad idea to use a "text" field as
    a foreign key. The reason being that if the "name" field changes in one table,
    the other table is not automatically updated. If this happens your data will be
    inconsistent and your queries may return incorrect results. A better structure
    would be to create a numeric ID in the "TechsTbl" table and reference that
    numeric ID in the "Help_Desk_List" table instead of the text "name".


    <cfquery datasource="HelpDesk" name = "reminder">
    SELECT a.entry_date, a.entry_description, a.emailto, a.remind, b.email
    FROM Help_Desk_List a, TechsTbl b
    WHERE a.duedate = #CreateODBCDate(now())#
    AND a.status = 'Open'
    AND a.remind = 'Yes'
    AND a.name = b.name
    </cfquery>







    mxstu Guest

Similar Threads

  1. #38787 [NEW]: Wrong line number in switch statement
    By rick at dualmedia dot it in forum PHP Bugs
    Replies: 1
    Last Post: September 12th, 10:08 AM
  2. What is wrong with this insert statement?
    By chill84 in forum Coldfusion Database Access
    Replies: 1
    Last Post: May 13th, 02:53 PM
  3. mssql: update statement wrong or truncated
    By Jochen Daum in forum PHP Development
    Replies: 1
    Last Post: November 16th, 11:01 PM
  4. Replies: 4
    Last Post: September 16th, 04:54 PM
  5. field seperator in select / wrong statement ?
    By Kai Baumgart in forum IBM DB2
    Replies: 1
    Last Post: August 26th, 02:56 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
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139