Professional Web Applications Themes

Problem querying Access DB for records within a certaindate range. - Coldfusion Database Access

Hi Everyone, I'm having a problem pulling records out of an Access DB that are between a certain date range. The records are located in a Date/Time field in my DB and the formatting for the field is set to General Date. I have a form where the user will enter in a FROM and TO daterange (ex. FROM: 12/1/2005 TO: 12/31/2005) and then I want to pull all the records within those dates. The dates/times in the DB are formatted like this: 12/17/2005 6:22:00 PM. My query looks something like this: <cfquery datasource="MyDatasource" name="MyQuery"> SELECT order_id, order_date, f_name, l_name ...

  1. #1

    Default Problem querying Access DB for records within a certaindate range.

    Hi Everyone,

    I'm having a problem pulling records out of an Access DB that are between a
    certain date range. The records are located in a Date/Time field in my DB and
    the formatting for the field is set to General Date. I have a form where the
    user will enter in a FROM and TO daterange (ex. FROM: 12/1/2005 TO:
    12/31/2005) and then I want to pull all the records within those dates. The
    dates/times in the DB are formatted like this: 12/17/2005 6:22:00 PM. My
    query looks something like this:

    <cfquery datasource="MyDatasource" name="MyQuery">
    SELECT order_id, order_date, f_name, l_name
    FROM orders
    WHERE order_date BETWEEN #form.from_date# AND #form.to_date#
    </cfquery>

    The problem is in the WHERE clause. I've never had a problem inserting
    dates/times into the DB, but I've actually never had a need to pull records by
    them until now. I know that dates/times are pretty sensitive as far as the
    type that is used, and I just don't know what syntax I should use in the WHERE
    clause.

    Any ideas would be much appreciated!

    Thanks,

    ORB

    liquidorb Guest

  2. #2

    Default Re: Problem querying Access DB for records within acertain date range.

    Try this

    WHERE order_date BETWEEN #CreateODBCDate(form.from_date)# AND #CreateODBCDate(form.to_date)#

    Ken

    The ScareCrow Guest

  3. #3

    Default Re: Problem querying Access DB for records within acertain date range.

    ScareCrow...you ROCK!

    Thanks a million, it worked perfectly right off the bat! I'd been struggling with that for hours. THANKS!
    liquidorb Guest

  4. #4

    Default Re: Problem querying Access DB for records within acertain date range.

    I do a lot of pages where the query is based on a date range.

    Before I even try my query, I always ensure that both form fields are valid
    dates, and they are in the proper order, and they are not too far apart, and
    that there is data in that range.

    Dan Bracuk Guest

  5. #5

    Default Re: Problem querying Access DB for records within acertain date range.

    Also to expand on Dan's responce.
    As the date fields also have time values
    I would also suggest manually checking the results, to ensure that the correct
    results are being returned.
    Sometimes because of the times, there might be some records missing from the
    ending date.

    Ken

    The ScareCrow Guest

  6. #6

    Default Re: Problem querying Access DB for records within acertain date range.

    Originally posted by: The ScareCrow
    Also to expand on Dan's responce.
    As the date fields also have time values
    I would also suggest manually checking the results, to ensure that the correct
    results are being returned.
    Sometimes because of the times, there might be some records missing from the
    ending date.

    Ken

    Whether or not the date includes the time depends on the database. I work
    with both.

    If they do, something like this will work

    where #yourdate# >= #startdate# and #yourdate# < dateadd(day, 1, #enddate#)

    As always, the syntax depends on the database.


    Dan Bracuk Guest

Similar Threads

  1. Querying two datasources (MS ACCESS)
    By RuBot in forum Coldfusion Database Access
    Replies: 8
    Last Post: August 11th, 05:06 PM
  2. querying field / column names in Access table
    By Brian Gallutia in forum Coldfusion - Getting Started
    Replies: 3
    Last Post: April 28th, 07:05 PM
  3. Querying a Query problem
    By cmreis in forum Macromedia ColdFusion
    Replies: 2
    Last Post: April 1st, 06:54 PM
  4. Querying for unmatched records in two tables
    By OldNapkin in forum Coldfusion Database Access
    Replies: 2
    Last Post: March 10th, 06:46 PM
  5. Replies: 6
    Last Post: June 30th, 11: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