Need Help with Holidays Counts

[ebarrera]

Hello everyone,

it's been a while, anyways, I've been trying to tackle this on my own for about a week and can't seem to solve this. Anyones help is much appreciated.

I have a query that calculates how many meetings a course meets per month depending on the start and end dates and the weekdays it meets.

Record# = 1
COURSE = ART-101-05
Start Date = 08/14/09
End Date = 09/15/09
Days = MW

M1Start = 08/14/09
M1End = 08/30/09
M1Mon = 3
M1Tues = 0
M1Wed =2
M1Thurs = 0
M1Fri = 0
M1Sat = 0
M1Sun = 0

M2Start = 09/01/09
M2End = 09/15/09
M2Mon = 2
M2Tues = 0
M2Wed =2
M2Thurs = 0
M2Fri = 0
M2Sat = 0
M2Sun = 0

I now need to exclude holidays from this query. I am unsure how to use a table that currently exists with dates that are considered holidays.

Is there a function that allows me to go out to a table and check if a holiday falls between M1Start and M1End and also meets on Monday?

 

[Galaxiom]

Count the number of meeting days in the Holiday table that fall between the start and end dates.

It would be best to integrate what you already have and the holiday detection code into a single function but here is an idea of what it might look like. Air code.

The function would have arguments StartDate, EndDate, WhatDay

DCount("DateField","tHoliday", "(DateField BETWEEN StartDate AND EndDate) AND WeekDay(DateField) = WhatDay")

Note this uses the vb number code for day. See WeekDay() function below.
I'm sure you could modify it for other day input as you seem to know about working with days in VBA.

You have probably already used these to get as far as you have but for the sake of anyone who stumbles on this later check out these functions.

DatePart
http://office.microsoft.com/en-au/access/HA012288121033.aspx

WeekDay
http://office.microsoft.com/en-us/access/HA012289331033.aspx

Format
http://office.microsoft.com/en-us/access/HA012288391033.aspx

 

[DCrake]

There is a sample mdb that may be of interest to you

Link

David

 

[ebarrera]

Thanks Galaxiom and DCrake, I will look at these and report back.

 

[ebarrera]

Okay so I was able to come up with a function that counts working days minus the holidays, but I don't think that this is the best solution to my problem. It takes forever to complete with 3000+ records. If anyone can come up with a better way to tackle this, I would appreciate.

---------------------------------------------------
Option Compare Database

Public Function WorkDaysCount(BegDate As Variant, EndDate As Variant,

WeekdayCodes As String, CourseCalendar As String) As Integer

Dim DateCnt As Variant
Dim EndDays As Integer
Dim EndHolidays As Integer
Dim CalQuery As String

BegDate = DateValue(BegDate)
EndDate = DateValue(EndDate)
DateCnt = BegDate
EndDays = 0
EndHolidays = 0
CalQuery = "q_tHolidays - " & CourseCalendar

Do While DateCnt <= EndDate
'Check to see if the class meets on Mondays
If WeekdayCodes Like "*M*" Then
If Not IsNull(DLookup("Date", CalQuery, "[Date]=#" & DateCnt & "#")) Then
If Weekday(DateCnt) = 2 Then
EndHolidays = EndHolidays + 1
End If
End If

If Weekday(DateCnt) = 2 Then
EndDays = EndDays + 1
End If
End If

'Check to see if the class meets on Tuesdays
If WeekdayCodes Like "*T*" Then
If Not IsNull(DLookup("Date", CalQuery, "[Date]=#" & DateCnt & "#")) Then
If Weekday(DateCnt) = 3 Then
EndHolidays = EndHolidays + 1
End If
End If

If Weekday(DateCnt) = 3 Then
EndDays = EndDays + 1
End If
End If


'Check to see if the class meets on Wednesdays
If WeekdayCodes Like "*W*" Then
If Not IsNull(DLookup("Date", CalQuery, "[Date]=#" & DateCnt & "#")) Then
If Weekday(DateCnt) = 4 Then
EndHolidays = EndHolidays + 1
End If
End If

If Weekday(DateCnt) = 4 Then
EndDays = EndDays + 1
End If
End If

'Check to see if the class meets on Thursdays
If WeekdayCodes Like "*H*" Then
If Not IsNull(DLookup("Date", CalQuery, "[Date]=#" & DateCnt & "#")) Then
If Weekday(DateCnt) = 5 Then
EndHolidays = EndHolidays + 1
End If
End If

If Weekday(DateCnt) = 5 Then
EndDays = EndDays + 1
End If
End If

'Check to see if the class meets on Fridays
If WeekdayCodes Like "*F*" Then
If Not IsNull(DLookup("Date", CalQuery, "[Date]=#" & DateCnt & "#")) Then
If Weekday(DateCnt) = 6 Then
EndHolidays = EndHolidays + 1
End If
End If

If Weekday(DateCnt) = 6 Then
EndDays = EndDays + 1
End If
End If

'Check to see if the class meets on Saturdays
If WeekdayCodes Like "*S*" Then
If Not IsNull(DLookup("Date", CalQuery, "[Date]=#" & DateCnt & "#")) Then
If Weekday(DateCnt) = 7 Then
EndHolidays = EndHolidays + 1
End If
End If

If Weekday(DateCnt) = 7 Then
EndDays = EndDays + 1
End If
End If

'Check to see if the class meets on Sundays
If WeekdayCodes Like "*U*" Then
If Not IsNull(DLookup("Date", CalQuery, "[Date]=#" & DateCnt & "#")) Then
If Weekday(DateCnt) = 1 Then
EndHolidays = EndHolidays + 1
End If
End If

If Weekday(DateCnt) = 1 Then
EndDays = EndDays + 1
End If
End If


DateCnt = DateAdd("d", 1, DateCnt)

Loop

WorkDaysCount = EndDays - EndHolidays
End Function