Originally Posted by
isladogs
Its a very neat solution - based on Chip Pearson's Excel code with minor modifications for Access
Very nice. Wrote one off sub to loop on 2017 to 2030 to add Easter dates to tblHolidaysUSA in post#6, this thread.
Anybody else who wants the update:
Code:
Public Function fcnGetEasterSunday(Yr As Integer) As Date
'Code taken from http://www.cpearson.com/excel/Easter.aspx
Dim D As Integer
D = (((255 - 11 * (Yr Mod 19)) - 21) Mod 30) + 21
fcnGetEasterSunday = DateSerial(Yr, 3, 1) + D + (D > 48) + 6 - ((Yr + Yr \ 4 + D + (D > 48) + 1) Mod 7)
End Function
Public Sub InsertEaster_tblHolidays()
Dim x As Integer
Dim sSQL As String
For x = 2017 To 2030
sSQL = "Insert into tblHolidaysUSA (DayofWeek, HolDate, HolidayName) Values(" _
& "'Sunday', #" & fcnGetEasterSunday(x) & "#, " & "'Easter'" & ")"
Debug.Print sSQL
CurrentDb.Execute sSQL, dbFailOnError
Next
End Sub
Put in a module and call from the immediate window. Run it only once!