Hi All
Please forgive cross-posting, also posted in
'microsoft.public.access.modulescoding'.
I have 'inherited' a part complete Access 2000 database which has in it the
module shown below. The idea is, I think, to take a number of days and a
start date then give the finish date excluding weekends and bank holidays.
The problem is that while it is excluding weekends it does not appear to be
looping through the dates in the table tbl_BankHols and excluding any dates
in there.
The system is UK date (dd/mm/yyyy), the table is UK short date, if I put a
UK formatted date in the immediate window I get a US (mm/dd/yyyy) answer, if
I put US date in I get a UK answer. Neither of which remove the bank
holidays!
Can anyone help with this please?
Thanks
SteveB
Module:
Function AddHoliday(NumberOfDays As Long, DateFrom As Date) As Date
Dim dtmCurr As Date
Dim lngCount As Long
lngCount = 0
dtmCurr = DateFrom
Do While lngCount < NumberOfDays
Do
dtmCurr = DateAdd("d", 1, dtmCurr)
Loop Until Weekday(dtmCurr, 7) >= 3 And _
DCount("*", "tbl_BankHols", "HolidayDate = " & (dtmCurr)) = 0
lngCount = lngCount + 1
Loop
AddHoliday = dtmCurr
End Function
|