Working days module help for noob

[AndycompanyZ]

I am trying to work out on a form how to calculate how many working days are subtracted forma date. On a form I have a date of an event and on a subform I have a combobox that the user selects from a number of options what kind of communications are needed for this event. For instance I have one that will select an invite (1) which will notify the user that an invite needs to be sent 7 days before the event. This then fills in a form field showing the date when it needs to be sent. I have this working correctly as below:

 
If Me.cmboCommunicationName.Value = 1 Then
Me.CommunicationDueDate = DateAdd("d", -7, Forms![frmMainNavigation]![NavigationSubform].Form![EventStartDayDate])

But I need to work out the date taking into account weekends (and better still public holidays as well) so that the communication is not sent out on a weekend as if the event fell on a Tuesday and as one of the options sends out a communication 3 days before it would be sent out on a saturday and if the recipient didn't get their email until they came into work they wouldn't get it until monday the day before the event!
I have read quite a bit about this and realise that I have to put a function into a module to work out the weekdays. But I have no idea how to go about putting the function into the code. I have found some code online that will work it out as belowthat I have adapted for the UK holidays this year:

 
Option Compare Database

Option Explicit
Const vbNewYear = "01/01/"
Const vbChristmasDay = "25/12/"
Const vbBoxingDay = "26/12/"

Public Function DaysInMonth(ByVal dteMonth As Date) As Integer
    Dim dteStart As Date
    Dim dteEnd As Date
    
    dteStart = DateSerial(Year(dteMonth), Month(dteMonth), 1)
    dteEnd = DateAdd("m", 1, dteStart) - 1
    
    DaysInMonth = CalculateDays(dteStart, dteEnd, True)
    
End Function
Public Function CalculateDays(ByVal dteStart As Date, _
    ByVal dteEnd As Date, Optional Include As Boolean) As Long
    Dim dteTemp As Date
    Dim lngDays As Long
    dteTemp = dteStart
    
    Do While dteTemp <> dteEnd
        If Not IsHoliday(dteTemp) Then
            Select Case Weekday(dteTemp)
                Case vbMonday, vbTuesday, vbWednesday, vbThursday, vbFriday
                    lngDays = lngDays + 1
            End Select
        End If
        dteTemp = DateAdd("d", 1, dteTemp)
    Loop
    If Include = True Then lngDays = lngDays + 1
    
    CalculateDays = lngDays
End Function
Public Function IsHoliday(ByVal dteTemp As Date) As Boolean
    On Error GoTo Err_IsHoliday
    
    Dim intYear As Integer
    
    intYear = Year(dteTemp)
    
    If DCount("HolidayID", "tblHolidays", "HolidayDate = #" & dteTemp & "#") > 0 Then
        IsHoliday = True
        Exit Function
    Else
        Select Case dteTemp
            Case Is = CDate(vbNewYear & intYear)
                IsHoliday = True
                Exit Function
            Case Is = GoodFriday(intYear)
                IsHoliday = True
                Exit Function
            Case Is = EasterMonday(intYear)
                IsHoliday = True
                Exit Function
            Case Is = EarlySpringBankHoliday(intYear)
                IsHoliday = True
                Exit Function
            Case Is = LateSpringBankHoliday(intYear)
                IsHoliday = True
                Exit Function
            Case Is = SummerBankHoliday(intYear)
                IsHoliday = True
                Exit Function
            Case Is = CDate(vbChristmasDay & intYear)
                IsHoliday = True
                Exit Function
            Case Is = CDate(vbBoxingDay & intYear)
                IsHoliday = True
                Exit Function
            Case Else
                IsHoliday = False
        End Select
    End If
    
Exit_IsHoliday:
    Exit Function
Err_IsHoliday:
    IsHoliday = False
    Resume Exit_IsHoliday
End Function
 
Private Function EarlySpringBankHoliday(ByVal intYear As Integer) As Date
    Dim dteStart As Date, intWeekDay As Integer
    dteStart = DateSerial(intYear, 5, 2)
    intWeekDay = Weekday(dteStart)
    dteStart = DateAdd("d", IIf(2 < intWeekDay, 7 - intWeekDay + 2, 2 - intWeekDay), dteStart)
    EarlySpringBankHoliday = dteStart
End Function
Private Function LateSpringBankHoliday(ByVal intYear As Integer) As Date
    Dim dteStart As Date, intWeekDay As Integer
    dteStart = DateSerial(intYear, 5, 30)
    intWeekDay = Weekday(dteStart)
    dteStart = DateAdd("d", IIf(2 < intWeekDay, 7 - intWeekDay + 2, 2 - intWeekDay), dteStart) - 7
    LateSpringBankHoliday = dteStart
End Function
Private Function GoodFriday(ByVal intYear As Integer) As Date
    GoodFriday = DateAdd("d", -2, Easter(intYear))
End Function
Private Function Easter(ByVal intYear As Integer) As Date
    Dim intDominical As Integer, intEpact As Integer, intQuote As Integer
    intDominical = 225 - (11 * (intYear Mod 19))
    If intDominical > 50 Then
        While intDominical > 50
            intDominical = intDominical - 30
        Wend
    End If
    If intDominical > 48 Then intDominical = intDominical - 1
    intEpact = (intYear + Int(intYear / 4) + intDominical + 1) Mod 7
    intQuote = intDominical + 7 - intEpact
    If intQuote > 31 Then
        Easter = DateSerial(intYear, 4, intQuote - 31)
    Else
        Easter = DateSerial(intYear, 3, intQuote)
    End If
End Function
Private Function EasterMonday(ByVal intYear As Integer) As Date
    EasterMonday = DateAdd("d", -2, Easter(intYear))
End Function
Private Function SummerBankHoliday(ByVal intYear As Integer) As Date
    Dim dteStart As Date, intWeekDay As Integer
    dteStart = DateSerial(intYear, 8, 29)
    intWeekDay = Weekday(dteStart)
    dteStart = DateAdd("d", IIf(2 < intWeekDay, 7 - intWeekDay + 2, 2 - intWeekDay), dteStart) - 7
    SummerBankHoliday = dteStart
End Function

But I have no idea what to do with this as it seems to my limited knowledge to have a number of functions in it and have no idea what to do next. I imagine it is something along the lines of:

 
If Me.cmboCommunicationName.Value = 1 Then
Me.CommunicationDueDate = CalculateDays("#Forms![frmMainNavigation]![NavigationSubform].Form![EventStartDayDate]#", 7, False)
End If

But I have no idea at all.
Can anyone point me where to look for help on this please or an easy way of doing this. Thank you all.

[ssanfu]

See: Doing WorkDay Math in VBA

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

[AndycompanyZ]

Thanks for the link. As I said in my original post I am a complete newbie to functions and am at a loss as to how to call this function or indeed what part of the linked code to call within a piece of code. Do I need to just copy the code into a module (or class module not sure which) and then put:
call "name of function"
I need to calculate the days from a form's field ie if the date I need is 7 days before the date showing on the form I need it to fill in another field with what date to send the communication taking into account the weekend. I have no idea at all about how to do this but want to learn more is there anywhere I can read about functions at a very basic level to learn and understand. All the sites I've read ssem to assume a knowledge about functions and I just want somewhere where i can read from the start.

[AndycompanyZ]

I have tried a different function I found :

 
Function UpBusDays3(pstart As Date, _
                    pNum As Integer, _
                    Optional pAdd As Boolean = True) As Date
'*******************************************
'Purpose:   Add or subtract business days
'           from a date
'Coded by:  raskew
'Inputs:    1) +) ? UpBusDays3(#2/17/06#, 3, True)
'           2) -) ? UpBusDays3(#2/22/06#, 3, False)
'Output:    1) +) 2/22/06
'           2) -) 2/17/06
'*******************************************
                    
Dim dteHold As Date
Dim i       As Integer
Dim n       As Integer
    dteHold = pstart
    n = pNum
    For i = 1 To n
       If pAdd Then  'add days
          dteHold = dteHold + IIf(Weekday(dteHold) > 5, 9 - Weekday(dteHold), 1)
       Else          'subtract days
          dteHold = dteHold - IIf(Weekday(dteHold) < 3, Choose(Weekday(dteHold), 2, 3), 1)
       End If
    Next i
    UpBusDays3 = dteHold
    
End Function

And I was thinking this would work:

 
Private Sub cmboCommunicationName_AfterUpdate()

If Me.cmboCommunicationName.Value = 1 Then
Me.CommunicationDueDate = UpBusDays3("#Forms![frmMainNavigation]![NavigationSubform].Form![EventStartDayDate]#", 7, False)
End If
If Me.cmboCommunicationName.Value = 2 Then
Me.CommunicationDueDate = UpBusDays3("#Forms![frmMainNavigation]![NavigationSubform].Form![EventStartDayDate]#", 3, False)
End If
If Me.cmboCommunicationName.Value = 3 Then
Me.CommunicationDueDate = UpBusDays3("#Forms![frmMainNavigation]![NavigationSubform].Form![EventStartDayDate]#", 1, False)
End If
If Me.cmboCommunicationName.Value = 4 Then
Me.CommunicationDueDate = UpBusDays3("#Forms![frmMainNavigation]![NavigationSubform].Form![EventStartDayDate]#", 2, True)
End If
If Me.cmboCommunicationName.Value = 5 Then
Me.CommunicationDueDate = UpBusDays3("#Forms![frmMainNavigation]![NavigationSubform].Form![EventStartDayDate]#", 7, True)
End If
End Sub

But it gives me a type mismatch error which I hope is just to do with the date but as i say I'm still learning and I have little idea what I'm doing.

[ssanfu]

This goes into a standard module. The name of the module cannot be the same name as any other object. I would suggest "MyFunctions" (without the quotes)

 
Function UpBusDays3(pstart As Date, _
                    pNum As Integer, _
                    Optional pAdd As Boolean = True) As Date
'*******************************************
'Purpose:   Add or subtract business days
'           from a date
'Coded by:  raskew
'Inputs:    1) +) ? UpBusDays3(#2/17/06#, 3, True)
'           2) -) ? UpBusDays3(#2/22/06#, 3, False)
'Output:    1) +) 2/22/06
'           2) -) 2/17/06
'*******************************************
                    
Dim dteHold As Date
Dim i       As Integer
Dim n       As Integer
    dteHold = pstart
    n = pNum
    For i = 1 To n
       If pAdd Then  'add days
          dteHold = dteHold + IIf(Weekday(dteHold) > 5, 9 - Weekday(dteHold), 1)
       Else          'subtract days
          dteHold = dteHold - IIf(Weekday(dteHold) < 3, Choose(Weekday(dteHold), 2, 3), 1)
       End If
    Next i
    UpBusDays3 = dteHold
    
End Function

This goes in the after update event of the combo box. I re-wrote your function.....

Private Sub cmboCommunicationName_AfterUpdate()
   Dim pBaseDate As Date  'EventStartDayDate
   Dim pNumDays As Integer  'number of days
   Dim pP_M As Boolean    'plus or minus (Add or Subtract)

   pBaseDate = Forms![frmMainNavigation]![NavigationSubform].Form![EventStartDayDate]
'   pBaseDate = #8/1/2011#   'for testing - delete whenever.....

'for debugging - comment out or delete  when ready
MsgBox "The starting date is " & pBaseDate

   Select Case Me.cmboCommunicationName
      Case 1
         pNumDays = 7
         pP_M = False
      Case 2
         pNumDays = 3
         pP_M = False
      Case 3
         pNumDays = 1
         pP_M = False
      Case 4
         pNumDays = 2
         pP_M = True
      Case 5
         pNumDays = 7
         pP_M = True
   End Select
   
   Me.CommunicationDueDate = UpBusDays3(pBaseDate, pNumDays, pP_M)

End Sub

[AndycompanyZ]

Thank you so much that works perfectly. I understand a lot more now how it works and hopefully I will now be able to use UDFs a lot more thanks to your help.