Problem with contract dates

[KatyB]

I have a database for managing time related contracts.
I need to be able to run parameter queries but if I query the data by start date then and contracts which are current but started before the first date entered in the query (this is the same for end dates) are not shown.

Does anyone have a solution to get round this issue please?

Many thanks

 

[pr2-eugin]

Since we have no idea of how your data is organized or how you have used the Query, its hard to give an answer. Try this link : http://www.baldyweb.com/OverLap.htm

 

[KatyB]

Thanks for your reply Paul

I understand the process of the link you sent through but I don't know about Driver tables or how the references to forms!xxx fits in.

My underlying query is:

SELECT Savings.StartDate, Savings.EndDate, Savings.MaterialCode, Savings.OldValue, Savings.NewValue, Savings.Saving, Savings.ProjectedVolume, Savings.ProjectedSaving, Savings.Days, Savings.DailySaving, IIf(Now()>[EndDate],"Expired") AS Status, (Date()-[StartDate]) AS QueryDays, [EndDate]-[StartDate] AS ContractedDays, [ContractedDays]*[DailySaving] AS ExpiredSaving, [QueryDays]*[DailySaving] AS CurrentSaving
FROM Vendor INNER JOIN (PurchOrg INNER JOIN (Materials INNER JOIN Savings ON Materials.MaterialCode = Savings.MaterialCode) ON PurchOrg.POOrgID = Materials.POOrgID) ON Vendor.SAPID = Savings.SAPID
WHERE (((Savings.StartDate)>=[StartDate]));

but this doesn't allow for the overlapping records. I have a form related to this query.

The database is designed to look at buying contracts for chemicals which run over a period of time. My aim is to report savings so when the query runs it needs to include savings from all current contracts running so if I just add a parameter query to the start date any contracts which started before the start date are not included.

Many thanks for your help