Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2014
    Posts
    4

    Question Unanswered: Return results where any day within a date range coincides with parameter date range

    Hi all. Long time lurker, first time poster. Apologies if this has been asked before but I am struggling to find a concise way to search for an answer, since key words like "date parameters" turns up a million results.

    What I am trying to accomplish: I am designing a database for a nonprofit organization, whose program serves high-risk children in an inner city. I need a method to have Access return participants who were active during a specified time period. I have their program start date and program end date (so anything that falls in between, would be a "was active" date), but I want a user to be able to specify parameter dates for each report, so that I get a list of those children who were active at any time during the dates specified-- so anywhere there is an overlap.

    Example: Tom and Jerry are children who were at one time enrolled in the program. Tom has a program start date of 1/1/2010 and a program end date of 1/31/2010. Jerry has a program start date of 2/1/2010 and a program end date of 2/28/2010. When I run my report, I want to specify a parameter to return only those children who were active between 2/15/2010 and 10/1/2010.
    Given those parameters, the report should return Jerry, because he was active at least 1 day during my specified time frame, even though he wasn't active for all of them. It shouldn't return Tom.
    This is, of course, a simple example since most children enroll and leave multiple times, enroll across multiple programs (the nonprofit has several), etc.

    I might be able to figure it out if the parameter for "was active" was a single day (i.e., does 2/15/2010 fall between Jerry's program start date and end date), but don't know how to do this for a range of dates, where not ALL have to match, just at least one.

    Thank you in advance.

    Details:
    Access 2013

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so you have a start date and end date, when a child is registered/enrolled, ie 'active'
    you want to find out what children were active during a user specified period

    it comes down to your where clause

    so you need to find the date banding from the user

    you want all children who whose
    'active' START date is less than or equal to the date band upper limit
    AND
    'active' END date is greater than or equal to the date band lower limit
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Apr 2014
    Posts
    4
    Well that was simple! Now I feel dumb.

    Thank you!!

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by DataQueen1987 View Post
    Well that was simple! Now I feel dumb.

    Thank you!!
    no need to feel dumb, we all have to start somewhere. if it helps I remember a colleague telling me "computers are thick, you need to be explicit in what you want a computer to do"
    I'd rather be riding on the Tiger 800 or the Norton

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •