Need help with this Date Range Query Please

[oldteddybear]

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
11/03/2012 30/03/2012
30/03/2012 02/04/2012
02/04/2012

I need a query 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. 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. This is my calculation to date and I need help please

SELECT T_Placements.ServiceStartDate, T_Placements.ServiceEndDate
FROM T_Placements
GROUP BY T_Placements.ServiceStartDate, T_Placements.ServiceEndDate
[FONT=&quot]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])));[/FONT]

 

[pbaldy]

See if this helps:

http://www.baldyweb.com/OverLap.htm

 

[oldteddybear]

Hi Paul, I have tried to use what I think your answer advises but I just dont seem to be able to get it to work. I suspect the fault is mine thru inexperience rather than your answer. I have interchanged field names between yours and mine trying to make it run but no luck yet.
Regards
Don

 

[pbaldy]

Hard to say what you might have done wrong, not being able to see it and all.

Can you post the db, or at least the SQL you tried?

 

[oldteddybear]

Hi Paul,
This is the SQL I am having problems with! I just seem to be going in circles with it.

SELECT T_Placements.ServiceStartDate, T_Placements.ServiceEndDate
FROM T_Placements
GROUP BY T_Placements.ServiceStartDate, T_Placements.ServiceEndDate
[FONT=&quot]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])));[/FONT]

 

[sparks80]

Criteria Start Date: StartA
Criteria End Date: EndA
Actual Start Date: StartB
Actual End Date: EndB

The following criteria will return true if the two sets of dates overlap:
(StartA <= EndB) and (StartB <= EndA)

This will not give you the overlapping dates, but should give you a start.

Credit goes to Charles Bretana here: http://stackoverflow.com/questions/325933/determine-whether-two-date-ranges-overlaphttp://stackoverflow.com/users/32632/charles-bretana

 

[oldteddybear]

Hi, Thanks for that however it just seems to identify in a different way the same results I am already getting. I do not seem to be able to gather in null values from the unfinished date ranges or dates that overlap which commenced prior to my query date.

 

[pbaldy]

Hi Paul,
This is the SQL I am having problems with! I just seem to be going in circles with it.

That's not the method I suggested. Can you post the db here?

 

[sparks80]

The information on baldyweb is exactly what you need.
You just need to add an exception to handle when the ServiceEndDate is null.

The solution is hopefully simpler than what you've been trying:

T_Placements.ServiceStartDate <= [End Date] And (T_Placements.ServiceEndDate >= [Start Date] OR T_Placements.ServiceEndDate Is Null)

 

[oldteddybear]

Thanks Paul and `Sparks'. Unfortunately cant post the data base as its in use with too much confidential information. However working with Sparks answer this creates a reply but I need a way of telling the query to replace any date prior to the query date with the actual query date. I keep trying IIF statements and they just tend to wipe out all the reply. So if the query returned a date range from mid february thru March the result should only show the dates from 1st March onward. I have a similay issue with the unentered dates at the other end. The Null statement certainly identifies them but I need it to return the query end date instead of an empty cell.
Any Thoughts?

 

[pbaldy]

I can understand not posting confidential info, but it's hard for me to create SQL in my head sometimes. How about a sample db with fake but representative data.

 

[Pat Hartman]

I don't have time to decode your statement now (too many unnecessary parens, too many brackets make it very hard to read) but it looks like at least one of the IIf statements will return null because no false value is specified so you break it out and remove all the extraneous punctuation so it is readable and you may see the error. Your problem could also be caused because the date format in the UK is not the "preferred" date format of SQL. And finally, the issue may be that you have time in the date fields if you used Now() to populate them rather than Date(). To extract the date only from a date/time field use the DateValue() function. To extract only time, use TimeValue().

 

[AkinwaleAkin]

I belief this will solve ur problem. Note that Its always good also to restrict ur users to valid entries in this case U can use a combobox to collect only valid dates existing by setting the rowsource to the field in the table.
I also wonder what ur reason is to Group the query because if u a talking about Placements, the records are definitely going to be unique which makes Grouping unnecessary and therefore the Query2 will do it fine.

Query1 (Only if Grouping is neccessary)

SELECT T_Placements.ServiceStartDate, T_Placements.ServiceEndDate
FROM T_Placements
GROUP BY T_Placements.ServiceStartDate, T_Placements.ServiceEndDate
HAVING (((T_Placements.ServiceStartDate)>=[Start Date]) AND ((T_Placements.ServiceEndDate)<=[End Date])) OR (((T_Placements.ServiceEndDate) Is Null));


Query2 (No Grouping)

SELECT T_Placements.ServiceStartDate, T_Placements.ServiceEndDate
FROM T_Placements
WHERE (((T_Placements.ServiceStartDate)>=[Start Date]) AND ((T_Placements.ServiceEndDate)<=[End Date])) OR (((T_Placements.ServiceEndDate) Is Null));


Regards,

AkinwaleAkin.

 

[pbaldy]

Akinwale, if you review the visual in my link you'll see that you criteria will miss more than one of the scenarios.

 

[oldteddybear]

Hi Paul,
The mdb file is attached as requested. I have removed many additional tables and fields to leave those that are relevant to my query. I hope this helps. I am unable to upload the appn front end as it exceeds the limit allowed by the forum and I dont know how to reduce it.
Cheers
Don

 

[pbaldy]

Does this work?

SELECT T_Placements.PlacementNumber, T_Placements.ClientID, T_Placements.ServiceStartDate, T_Placements.ServiceEndDate, nz([ServiceEndDate],#12/31/2029#) AS EndDateIfNull
FROM T_Placements
WHERE nz([ServiceEndDate],#12/31/2029#)>=[Forms]![frmTest]![txtStartDate] AND T_Placements.[ServiceStartDate]<=[Forms]![frmTest]![txtEndDate]

 

[oldteddybear]

Hi Paul, I hate to say it but no it did not. It just picked up every period of use entry in the data base with end dates in 2029. However it may be in the right direction.

So I changed the #12/31/2029# to [Start Date] and the entry [Forms]![frmTest]![txtStartDate] to [Start Date] and the entry [Forms]![frmTest]![txtEndDate] to [End Date]. I ran the query again and got a valid field of {ServiceStartDates] and a complete Field of [EndDateIfNull].
I'm not sure where I am going withthis but I am thinking along these lines
That the calculation then needs to look at the {ServiceStartDates} and if they are before the [Start Date] with a < [EndDateIfNull] entry the ServiceStartDate needs ammending to the [Start Date] in a new field called[Final Start Date]. This would then allow a calculation to follow where the [Final Start Date] is subtracted from the [EndDateIfNull] field to give the relevant days of service for each field thru the query time frame. Exactly what this will look like in a SQL has me puzzled and needing another coffee or two.

 

[pbaldy]

When I run it for March 1 thru March 5, I get 33 records, most of which clearly overlap that range. 4 of the records are included that have no end dates, but the start date is before or during that period. See attached. What records would you expect to be returned, specifically, if not these?

 

[oldteddybear]

Mm. When I copy and paste without changes I get 1817 incomplete records. When I make the changes as above I still get 1817 records except the [EndDateIfNull] field now returns results with all cells occupied and those over the query date with the correct substitution. Hence I think we on the right track but now need to eliminate completed records that occurred prior to the query [Start Date]