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:
SELECT T_Placements.ServiceStartDate, T_Placements.ServiceEndDate
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])));
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!?