Check and ignore Bank Holidays

[Kasher]

Hi I am new to VBA. I have got the follwing code which is working fi9. Now I want this code to check and ignore the bank holidays as well as it is excluding the saturday and sunday. how do i do it.

I Have got one table with fields, id (autonumber), pin no(text), date taken (date) and type (text). another table for Bank holidays with a single field, bank_holidays(date).

---------
Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim dteIterator As Date

dteIterator = Me!StartDate

While dteIterator <= Me!EndDate
If Weekday(dteIterator, vbSunday) <> vbSaturday And _
Weekday(dteIterator, vbSunday) <> vbSunday Then
DoCmd.RunSQL "INSERT INTO tblMain([pin_No],[DateTaken], [Type]) VALUES ('" & _
Me!pin2 & "', #" & Format(dteIterator, "mm/dd/yyyy") & "#, '" & _
Me!type2 & "');"

End If
DoCmd.SetWarnings False
dteIterator = DateAdd("d", 1, dteIterator)


Wend
End Sub

 

[gemma-the-husky]

note you need vbsaturday in the first

If Weekday(dteIterator, vbSunday) <> vbSaturday And _
Weekday(dteIterator, vbSunday) <> vbSunday Then


this sort of logic would do it (code fragment)

if weekday(activedate)=vbsaturday then goto skip
if weekday(activedate)=vbsunday then goto skip
if bankholiday(activedate)=true then goto skip

insert statement ...

skip:
increment activedate


you need a function to determine whather a date is a bank holiday.

alternatively - insert all the dates - then delete the dates which are bank holidays. this is probably more efficient in code instruction terms.

 

[Kasher]

thanks for the reply, I already mentioned that I am new to vBA. i did not understand your code. So can you please explain it to me. and how can i write that function to check bank holidays.

 

[Kasher]

I have found the solution from "fraseatwork" post. here is the code;

Option Compare Database

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim dteIterator As Date

dteIterator = Me!StartDate
While dteIterator <= Me!EndDate

If IsWorkDay(dteIterator) = True Then

DoCmd.RunSQL "INSERT INTO tblMain([pin_No],[DateTaken], [Type]) VALUES ('" & _
Me!pin2 & "', #" & Format(dteIterator, "mm/dd/yyyy") & "#, '" & _
Me!type2 & "');"

End If
DoCmd.SetWarnings False
dteIterator = DateAdd("d", 1, dteIterator)


Wend
End Sub

[U][COLOR="red"]'1. Checks if a date is a bank holiday[/COLOR][/U]
Public Function IsBankHoliday(ByVal theDate As Date) As Boolean
Dim hols As New Collection
Dim d As Variant

Set hols = Nothing

hols.Add #3/29/2013#
hols.Add #4/1/2013#
hols.Add #5/27/2013#
hols.Add #5/27/2013#
hols.Add #8/26/2013#
hols.Add #12/25/2013#
hols.Add #12/26/2013#
hols.Add #1/1/2014#
hols.Add #4/18/2014#
hols.Add #4/21/2014#
hols.Add #5/5/2014#
hols.Add #5/26/2014#
hols.Add #8/25/2014#
hols.Add #12/25/2014#
hols.Add #12/26/2014#

For Each d In hols
If d = theDate Then
IsBankHoliday = True
Exit Function
End If
Next d

Set hols = Nothing

End Function

[U][COLOR="Red"]'2. Checks if a date is a work day (i.e. not a bank holiday or weekend)[/COLOR][/U]
Public Function IsWorkDay(ByVal theDate) As Boolean
If IsBankHoliday(theDate) Then
IsWorkDay = False
Exit Function
ElseIf (Weekday(theDate) = 1) Or (Weekday(theDate) = 7) Then
IsWorkDay = False
Exit Function
Else
IsWorkDay = True
End If

End Function

thanks...