Results 1 to 3 of 3
  1. #1
    ARickert's Avatar
    ARickert is offline Novice
    Windows Vista Access 2007
    Join Date
    Dec 2010
    Location
    Kansas
    Posts
    22

    Run query by date range

    I need to run a query or report ranging from one date to another (ie: 5/25/2011 - 6/25/2011). I don't understand how to do the "start date" "end date" for the query if I only have one date field in my original table.

  2. #2
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    In the query you have to specify the 2 dates of the range by using the BETWEEN...AND.... clause in the query's criteria section

    Since SQL view is easier to show here, the query would look something like this

    SELECT field1, field2
    FROM tablename
    WHERE yourdatefield BETWEEN [startdate] AND [enddate]

    You can also use an unbound form to supply the dates of the range; that WHERE clause would look like this

    WHERE yourdatefiel BETWEEN forms!formname!controlnameforstartdate AND forms!formname!controlnameforenddate

  3. #3
    boblarson is offline --------
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jun 2011
    Posts
    1,272
    And my suggestion is to not put the criteria in the query and just use the opening of the report to do it (as then you can have a more generic report which doesn't necessarily have to supply a date if you find you don't want to at some point in time but want to use other criteria instead. You can also supplement the criteria at any time to have even another report.

    So, the open command would be:

    DoCmd.OpenReport "YourReportNameHere", acViewPreview, , "[yourDateFieldNameHere] Between " & Format(Forms!FormName.StartDateControlName, "\#mm\/dd\/yyyy\#") & " And " & Format(Forms!FormName.EndDateControlName, "\#mm\/dd\/yyyy\#")

    But jzwp11's way will definitely work as well. I just find that it can be good to have a single generic report and then pass the where clause to it when opening to give you more flexibility.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Date range
    By eacollie in forum Queries
    Replies: 7
    Last Post: 06-05-2011, 03:38 PM
  2. Complicated Query needs some date range help
    By KevinMCB in forum Queries
    Replies: 1
    Last Post: 01-11-2011, 12:25 PM
  3. Date range help!!
    By bananatodd in forum Access
    Replies: 26
    Last Post: 10-18-2010, 01:57 PM
  4. Date range query from form
    By Steve Barnes in forum Queries
    Replies: 2
    Last Post: 07-29-2010, 07:06 PM
  5. Query for date range from two column?
    By sunny in forum Queries
    Replies: 2
    Last Post: 07-28-2010, 07:12 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums