Professional Web Applications Themes

createodbcdate - Coldfusion - Advanced Techniques

Hello, I have a form problem where I am trying to pull results from a database by using a start date, end date and type of service in my form. Then I need to display all results falling in between those two dates provided that match the service. The data is in an Access database and is in Date/Time format. I was successful in doing this a long time ago by using CreateODBCDate, but I can't remember how I did this. I keep getting errors and it will not display any results. Below is my form: <form name="search" action="myactionpage.cfm" ENCTYPE="multipart/form-data" ...

Sponsored Links
  1. #1

    Default createodbcdate

    Hello,
    I have a form problem where I am trying to pull results from a database by
    using a start date, end date and type of service in my form. Then I need to
    display all results falling in between those two dates provided that match the
    service. The data is in an Access database and is in Date/Time format.

    I was successful in doing this a long time ago by using CreateODBCDate, but I
    can't remember how I did this. I keep getting errors and it will not display
    any results.

    Below is my form:

    <form name="search" action="myactionpage.cfm" ENCTYPE="multipart/form-data"
    method="post">
    <font face="verdana, arial" size="1" color="#000000">
    Start Date:<br>
    <select name="StartDate">
    <cfoutput query="qdates">
    <option value="#ServiceDate#">#DateFormat(ServiceDate, 'mm/dd/yy')#</option>
    </cfoutput>
    </select>
    <br><br>
    End Date:<br>
    <select name="EndDate">
    <cfoutput query="qdates">
    <option value="#ServiceDate#">#DateFormat(ServiceDate, 'mm/dd/yy')#</option>
    </cfoutput>
    </select>
    <br><br>
    Service:<br>
    <select name="Service">
    <cfoutput query="qservice">
    <option value="#Service#">#Service#</option>
    </cfoutput>
    </select>
    </font>
    <br><br>
    <input type="submit" value="search">
    </form>


    Below is my action page:

    <cfquery name="qstudentreport" datasource="mydatabase">
    SELECT * FROM HoursLog WHERE Service = '#form.Service#' AND ServiceDate
    BETWEEN '#CreateODBCDate(form.StartDate)#' AND '#CreateODBCDate(form.Enddate)#'
    </cfquery>

    <cfoutput query="qstudentreport">

    <table cellspacing="2" cellpadding="2" border="1">
    <tr>
    <td valign="top">#ServiceDate#</td>
    <td valign="top">#Service#</td>
    </tr>
    </table>
    </cfoutput>

    Thanks for any help on this in advance!

    Sponsored Links
    brianism Guest

  2. #2

    Default Re: createodbcdate

    btw, instead of looping through your queries twice (Since the options output is
    identical), you could use cfsavecontent.

    <cfsavecontent variable="options">
    <cfoutput query="qdates">
    <option value="#startdate#" <cfif qdates.currentrow eq
    1>selected</cfif>>#DateFormat(startdate, 'mm/dd/yy')#</option>
    </cfoutput>

    Then output like so...

    <select name="EndDate">
    <cfoutput>#options#</cfoutput>
    </select>

    </cfsavecontent>


    BSterner Guest

  3. #3

    Default Re: createodbcdate

    got rid of the single quotes and it works great! Can't believe I missed that, I tried everything but! Thanks!
    brianism Guest

Similar Threads

  1. Coldfusion - search parameters - CreateODBCDate
    By James890 in forum Coldfusion Database Access
    Replies: 3
    Last Post: April 6th, 04:20 PM
  2. #CreateODBCDate(Form.DateOfBirth)#
    By susanring in forum Coldfusion - Getting Started
    Replies: 4
    Last Post: May 10th, 09:29 PM
  3. Usage of CreateODBCDate
    By am2605 in forum Coldfusion Database Access
    Replies: 1
    Last Post: March 3rd, 04:17 AM
  4. CreateODBCDate
    By atkearns in forum Coldfusion Database Access
    Replies: 7
    Last Post: November 10th, 12:38 AM

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