Results 1 to 4 of 4
  1. #1
    kagoodwin13 is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Feb 2012
    Posts
    181

    Query input dates for range of dates in two fields

    Hi all, this is my first post. I seem to be stuck on an issue with taking dates as input and querying a range of dates between two fields.



    I am currently making a database of contract data. Each contract has an effective date (field StarDate) and an ending date (field EndDate). What I would like to do is eventually create a form-based query to input a range of two dates, and the query would return all contracts that were effective between those two dates.

    For instance, if I would like search all contracts valid in 2007, I would input 1/1/2007 and 12/31/2007 into the form fields, and the query would return contracts signed between 1/1/2007-12/31/2007 and contracts ending between 1/1/2007-12/31/2007.

    I've seen a lot of solutions for taking input of two dates and searching a single date field with BETWEEN queries, and I've also seen solutions for taking a single date input and searching a range of dates with IIF statements, but nothing to query two input dates vs two date fields.

    Any thoughts?

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,715
    Read this article by Allen Browne for ideas
    http://allenbrowne.com/casu-08.html

  3. #3
    kagoodwin13 is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Feb 2012
    Posts
    181
    Thanks for the help orange, but I found the solution here.

    Code:
    SELECT [Example Data].ID, [Example Data].[StarDate], [Example Data].[EndDate]
    FROM [Example Data]
    WHERE ((([Example Data].[StarDate])<[Forms]![DateSearch]![EndDate]) AND (([Example Data].[EndDate])>[Forms]![DateSearch]![StartDate]));
    or for the non-SQL savvy (like me):

    Under StarDate, put in the Criteria field: "<[Forms]![DateSearch]![EndDate]." Under EndDate, put in the Criteria field: ">[Forms]![DateSearch]![StartDate]"

    Seems so simple now that I have it figured out!

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,715
    Great work. Glad you found a solution.

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

Similar Threads

  1. Replies: 10
    Last Post: 11-16-2011, 10:58 AM
  2. Replies: 2
    Last Post: 02-02-2011, 06:39 AM
  3. Replies: 13
    Last Post: 09-27-2010, 03:10 PM
  4. Replies: 3
    Last Post: 09-29-2009, 07:08 AM
  5. Count Dates within Date Range
    By eckert1961 in forum Queries
    Replies: 2
    Last Post: 03-08-2009, 10:58 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