Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2009
    Waikato, New Zealand

    Unanswered: Capturing Date ranges I need help please!

    I need a query, (See SQL statement below), that identifies all usages between two given dates. My problem is that I cannot get my formula to pick up period within the range when the start or finish date is outside the Query Start Date (For an example lets assume that I want all dates in March from the table below). It also needs to identify those periods within the range that end after the Query End Date for which no [ServiceEndDate] has yet been set.
    I have two entry fields in a Table defining the [ServiceStartDate] and [ServiceEndDate] of times clients enter and leave a service. Similar to this example:
    [ServiceStartDate] [ServiceEndDate]

    22/12/2011 22/01/2012
    22/01/2012 24/01/2012
    24/01/2012 09/03/2012
    09/03/2012 11/03/2012
    30/03/2012 02/04/2012

    SELECT T_Placements.ServiceStartDate, T_Placements.ServiceEndDate
    FROM T_Placements
    GROUP BY T_Placements.ServiceStartDate, T_Placements.ServiceEndDate
    HAVING (((T_Placements.ServiceStartDate)=IIf(([ServiceStartDate]<[Start Date]) And ([ServiceEndDate]>[Start Date]),[Start Date])) AND ((T_Placements.ServiceEndDate)=(IIf(([ServiceEndDate])>=[End Date],[End Date],[ServiceEndDate])) And (T_Placements.ServiceEndDate)=IIf("Nz[serviceEndDate]]",[End Date]))) OR (((T_Placements.ServiceStartDate)=IIf([ServiceStartDate]>=[Start Date],[ServiceStartDate])));
    Last edited by oldteddybear; 04-03-12 at 08:37.

  2. #2
    Join Date
    Apr 2004
    Derbyshire, UK
    Provided Answers: 2

    On the basis that you query actally runs and produces some data, then I think I would try changing the HAVING clause to a WHERE clause (which will need moving to before the GROUP BY clause) and see what that yield!?


Posting Permissions

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