exclude public holiday according country state????

jocelyn_ooi

Registered User.
Local time
Yesterday, 18:32
Joined
Sep 27, 2011
Messages
17
Hi there, i already success to exclude public holiday when user apply for annual leave. But now i have 1 problem is if user work on different state so they will have different holiday. So, can anyone suggest a method to solve this problem??
 
i have create a table holidays and written code on module... the following is my code.
ublic Function Work_Days(BegDate As Variant, EndDate As Variant) As Integer
' Note that this function does account for holidays.
Dim WholeWeeks As Variant
Dim DateCnt As Variant
Dim EndDays As Integer
Dim EmpLocation As Integer

On Error GoTo Err_Work_Days

BegDate = DateValue(BegDate)
EndDate = DateValue(EndDate)
WholeWeeks = DateDiff("w", BegDate, EndDate)
DateCnt = DateAdd("ww", WholeWeeks, BegDate)
EndDays = 0

Do While DateCnt <= EndDate
If Not IsNull(DLookup("HolidayDate", "tblHolidays", "[HolidayDate]=#" & Format(DateCnt, "mm/d/yyyy") & "#")) Then
EndDays = EndDays - 1
End If

If Format(DateCnt, "ddd") <> "Sun" And Format(DateCnt, "ddd") <> "Sat" Then
EndDays = EndDays + 1
End If
DateCnt = DateAdd("d", 1, DateCnt)
Loop

Work_Days = WholeWeeks * 5 + EndDays

Exit Function



Err_Work_Days:
' If either BegDate or EndDate is Null, return a zero
' to indicate that no workdays passed between the two dates.
If Err.Number = 94 Then
Work_Days = 0
Exit Function
Else
' If some other error occurs, provide a message.
MsgBox "Error " & Err.Number & ": " & Err.Description
End If
End Function
 
Do you have the state holiday data in another table? Where will you be pulling the by-state holiday data?
 
YES.. i include the state data on table holidays as well. But i dunno how to coding to count the days according to state.
 

Users who are viewing this thread

Back
Top Bottom