Problems with Dates in a query

[oldteddybear]

I have two date columns [ServiceStartDate] and [ServiceEndDate] in my data base. I am trying to run a query that either makes the date of the enquiry period [Start Date] as the [ServiceStartDate] where that date is prior to the Enquiry date [Start Date] or if the [ServiceStartDate] is equal to or after the Enquiry date then to use the [ServiceStartDate].
In Design view of the [ServiceStartDate] column, in the criteria row I have entered >=[Start Date] and in the or row I have entered IIf([ServiceStartDate]<[Start Date],[Start Date])

So I have ended up with an SQL stating




WHERE (((T_Placements.ServiceStartDate)>=[Start Date]) AND ((T_Placements.ServiceEndDate)<=[End Date])) OR (((T_Placements.ServiceStartDate)=IIf([ServiceStartDate]<[Start Date],[Start Date])) AND ((T_Placements.ServiceEndDate)=IIf("IsNull",Date() )))


If I use only the >=[Start Date] the query works with all dates after the date of Query but when I add the 2nd parameter it does not return anything.
Can anyone help?

[pbaldy]

Does this help?

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

[oldteddybear]

I appreciate your input. Your explanation is close to what I am looking for however It still does not pick up the dates either side of the query that run through the period in question. Your web description is close to what I am trying to achieve but I am expecting the query to sum the dates of each event occurring within the time frame.
This is the SQL Staement as it stands:
ELECT T_OrganisationPickList.Organisation, T_EnterClientDemographics.RefSite, Sum(([serviceEndDate])-([ServiceStartDate])) AS IndTotals, [ClientName]+" "+[ClientSurName] AS name, T_Placements.ServiceStartDate, T_Placements.ServiceEndDate
FROM T_ServiceType INNER JOIN (T_OrganisationPickList INNER JOIN (T_EnterClientDemographics INNER JOIN T_Placements ON T_EnterClientDemographics.ClientID = T_Placements.ClientID) ON (T_OrganisationPickList.OrgID = T_Placements.OrgID) AND (T_OrganisationPickList.OrgID = T_EnterClientDemographics.RefOrg)) ON T_ServiceType.ServiceTypeID = T_Placements.ServiceTypeID
WHERE (((T_Placements.ServiceStartDate)>=[Start Date]) AND ((T_Placements.ServiceEndDate)<=[End Date]))
GROUP BY T_OrganisationPickList.Organisation, T_EnterClientDemographics.RefSite, [ClientName]+" "+[ClientSurName], T_ServiceType.ServiceType, T_EnterClientDemographics.ClientName, T_EnterClientDemographics.ClientSurName, T_Placements.ServiceStartDate, T_Placements.ServiceEndDate

[pbaldy]

Did the method I posted not return the appropriate records? You're comparing start to start and end to end, which will not catch all records.