Edit Module

[dcollard23]

Is there a way to edit this module to where my start date may be on a weekend or holiday and if it is then the calculation between my two dates will start calculating on the following business day but when doing my calculation to obtain the end date holidays and weekends are taken out and the end date will fall on a business day?

Example: Startdate is 3/10/2012 (the transaction I am doing has to be completed in 1 day from the start date) so I am getting an end date of 3/12/2012 It looks as if the current module above is taking me back to a start date of 3/9/2012 then doing the calculation (taking out 3/10 and 3/11) giving me 3/12/2012 instead of giving me 3/13/2012 ( the entry date may be 3/10/2012 but the caculation should start on 3/12/12 giving me the end date of 3/13/2012). The start date can be a holiday also.

Public Function fAddBusinessDay(pStart As Date, pAdd As Integer)
'Adds the proper Business day skipping holidays and weekends
'adapted from code by Arvin Meyer 05/26/98
On Error GoTo Err_fAddBusinessDay
Do While pAdd > 0
pStart = pStart + 1
If Weekday(pStart) <> 1 And Weekday(pStart) <> 7 Then
'not Sunday nor Saturday
If DCount("*", "tblHolidays", "[holidaydate]=#" & pStart & "#") = 0 Then
'not holiday
pAdd = pAdd - 1
End If
End If
Loop
fAddBusinessDay = pStart
Exit_fAddBusinessDay:
Exit Function
Err_fAddBusinessDay:
MsgBox Err.Description
Resume Exit_fAddBusinessDay
End Function


Thanks for your assistance in advance.
I hope I wasn't too confusing.

I am looking for a solution. If possible please tell me what to do and how to do it.

Elaine

 

[Chrissy]

This should get you started and well on your way.


(I have a link that I cannot post ... which it won't allow me to do .... I'm going to get creative)

h t t p : / /
bytes.com/
topic/
access/
answers/
584593-determine-if-date-bank-holiday


I do not know of any module that is created exactly as you mentioned, but it is simple enough for most federal holidays to create your function to find out if a day is a holiday and adjust your days forward count.

 

[dcollard23]

I am going to check it out. Thanks for taking the time to respond to my post.
Elaine