Date Availabilty

[CutAndPaste]

I've got a table with items that are available with a [startdate] and an [enddate] for their availibility. I've also got a report I want to run that is date limited. Some items will start and end within the period, some will start before and end during, some start during and end after and some will start before and end after.

I'm running the reporting dates from a form field and will limit the query by that form.

I'm currently limiting the records in the query by:
StartDate
<=[Forms]![frmMenuMain]![EndDate]
EndDate
>=[Forms]![frmMenuMain]![StartDate] Or Is Null

Reporting Period is 01/01/2005 to 31/01/2005
e.g.
ItemA Start = 10/01/2005 End = 31/01/2005
ItemB Start = 15/12/20045 End = 25/021/2005
ItemC Start = 20/12/2004 End = 25/01/2005
ItemD Start = 22/01/2005 End = 19/02/2005

I need to know for each item, how many days it was available during the reporting period.

How can I do this please?

thanks

 

[FoFa]

I would create a VBA function and pull the data from that function.
AvailRptPeriod=CalcDaysAvail([StartDate], [EndDate]) ' this would be the call line
The function would need to be something like:
Function AvailRptPeriod(SDate as date, EDate as date) as integer
DIM W1Date as date, W2Date as date
' first calculate the proper end date
If isnull(EDate) then
W2Date = [Forms]![frmMenuMain]![EndDate]
Else
If EDate > [Forms]![frmMenuMain]![EndDate] then
W2Date = [Forms]![frmMenuMain]![EndDate]
ELSE
W2Date = EDate
END IF
END IF
' Calculate the proper start date
IF SDate < [Forms]![frmMenuMain]![StartDate] then
W1Date = [Forms]![frmMenuMain]![StartDate]
Else
W1Date = SDate
End If
' Calculate days available
AvailRptPeriod = DateDiff("d",W1Dae,W2Date)
exit function

Error detection would be nice, syntax was not checked, but you get the idea