Access VBA to exclude holidays and weekends from a datediff formula 1

[Aran10]

There are loads of these over the web, but none that would work for my table

"Public Function DateDiffW(BegDate As Date, EndDate As Date) As Integer
'Returns number of days, excluding Saturday and Sunday
'As written, counts both BegDate and EndDate, e.g.,
'Monday - Friday would count as 5 days
'coded by: raskew

Const SUNDAY = 1
Const SATURDAY = 7
Dim NumWeeks As Integer

If BegDate > EndDate Then
DateDiffW = 0
Else
Select Case Weekday(BegDate)
Case SUNDAY: BegDate = BegDate + 1
Case SATURDAY: BegDate = BegDate + 2
End Select
Select Case Weekday(EndDate)
Case SUNDAY: EndDate = EndDate - 2
Case SATURDAY: EndDate = EndDate - 1
End Select
NumWeeks = DateDiff("ww", BegDate, EndDate)
DateDiffW = NumWeeks * 5 + Weekday(EndDate) - Weekday(BegDate)
End If
End Function"

This is being used for my current VBA but it doesn't work for the specified holidays that I have in my table

These fields are called:

Autumn1_Start, Autumn1_End, Autumn2_Start, Autumn2_End, Spring1_Start, Spring2_End, Summer1_Start, Summer1_End, Summer2_Start, Summer2_End

Now the current datediffw formula does not work to exclude these term starts and ends from the date diff as i would require, can someone please edit the formula above to help it not to count the holidays

 

[PWise]

What i would do is create a calander table
Fields
Dateid autonumber
DayOfyear Date
Holiday yes/no
DayName text

Add all date as far back as you need and as far ahead as you need
(my calander table starts @ 7/1/03 and i add a year every year)
check of all holdays as yes
BegDate=dmin("Dayofyear","calander",BegDate & ">=" & BegDate & " and datepart('dw',dayofyear) between 2 and 6 and Holiday =0 )

EndDate =dmax("Dayofyear","calander",EndDate & "<=" & enddate & and datepart('dw',dayofyear) between 2 and 6 and Holiday =0)

 

[Aran10]

Thanks for the suggestion, but is there a way to do it with my current field setup using Vba or a query?

 

[jambai]

Check this out

http://www.mvps.org/access/datetime/date0012.htm

Thanks
Jambai