Find Date Match in a Date Range

[ncrawford]

Hello All

This is my first posting....and thanks in anticipation of help.

Using MS Access 2000 I am recording sick days absence for individual employees. A StartDate and EndDate are entered and the system calculates how many days leave taken for this period.

In addition the system looks back 12 months from the [StartDate] and calculates the sick days taken to date. e.g. StartDate is 16/07/07 - system looks back to 17/07/06 and calculates how many sick dates taken to 15/07/07.

The problem arises when the date rolling back 12 months e.g. 17/07/06 is in between an existing period of sick leave. The system currently ignores that whole period of leave instead of including the additional days in that period of leave (from and including 17/07/06).

I need a function that will look back twelve months from the start date of the current sick period and calculate sick days taken - has anybody out there got a function that already does this or any help finding the solution welcome.