Results 1 to 6 of 6
  1. #1
    dmm1977 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2017
    Posts
    5

    Query That Returns Clients In Program for Specific Time Frame

    I need to create a query that I will use to generate a report that shows how many clients were active in a given time frame. My client table is provided below:

    tblClients
    ClientID (PK)
    FirstName
    LastName
    ProgramStartDate
    ProgramEndDate



    If I want to determine how many clients were active between October of 2016 and October of 2017, how would I create a query that I can use to generate this report? I know that I will need to use the Program Start Date and Program End Date in creating this query, but I don't exactly know how to go about doing so.

  2. #2
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    Under the Program Start Date field, enter this criteria:
    Code:
    <=DateSerial(2017,10,31)
    and enter this criteria under Program end Date (on the same line as the other Criteria):
    Code:
    >=DateSerial(2016,10,1)

  3. #3
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    ...active between October of 2016 and October of 2017
    It will depend on what that means. Does it mean that a) both start and end date are within that period, b) either start or end date is within that period, or c) start date is before and end date is after that period, or can it be any of those conditions?

    Can you elaborate?

  4. #4
    dmm1977 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2017
    Posts
    5
    If I understand what you are asking I think it would be a combination of your options. As long as their end date is not before the first date of time frame and as long as their start date is not after the last date of the time frame, they would be considered active clients within that time period. I want to be able to enter any time frame I choose and get the active clients in that time frame. So if I have Client A (start date 09/04/16; end date 02/20/17), Client B (start date 12/14/16; end date 11/05/18) and Client B (start date 11/20/18; end date 04/16/19), only Client A and Client B would be considered active during a time frame of 10/01/16 through 10/31/17. I would prefer to be given a prompt that asks what time frame I want to search for. I hope that this makes sense.

  5. #5
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    Did you see/test my response?

    If you want it to be a parameter query, just change the hard-coded values to the parameters, i.e.

    Criteria under Program Start Date field:
    Code:
    <=[Enter End Date]
    Criteria under Program End Date field:
    Code:
    >=[Enter Start Date]
    If you want the Start Date prompt before the End Date prompt, just change the order in the query.

  6. #6
    dmm1977 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2017
    Posts
    5
    Good Morning JoeM,

    That worked perfectly! Thank you so much for your help.

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

Similar Threads

  1. Problem with specific query returns
    By rebfein in forum Queries
    Replies: 4
    Last Post: 08-04-2016, 12:50 PM
  2. Replies: 2
    Last Post: 10-30-2013, 11:40 AM
  3. Replies: 2
    Last Post: 05-11-2013, 10:14 AM
  4. Replies: 9
    Last Post: 02-12-2013, 03:14 PM
  5. Calculating age during a specific time frame
    By mommaof4kids in forum Reports
    Replies: 1
    Last Post: 09-06-2012, 06:08 PM

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