Add days while skipping weekends and holidays

[ST4RCUTTER]

There have been a number of posts that are similar, but not quite what I have been looking for. Most only check to see if the target date is a weekend or holiday, but I am looking to "skip/not count" any holiday or weekend day that occurs within the period of time.

For example: If given a start date of 5/20/2008, I want Access to count 10 days out while skipping weekends and any holiday (Memorial day 5/26/2008). This means that 5/24, 5/25, 5/26, 5/31, and 6/1 would not count towards the 10 days because they fall within it. This would give a target date of 6/3.

I tried putting together the following code:

Option Compare Database

Public Function NextBizDay(dteMyDate As Date, intAddDays As Integer) As Date
Dim NYD As Date 'New Years Day
Dim MEM As Date 'Memorial Day
Dim IND As Date 'Independence Day
Dim LAB As Date 'Labor Day
Dim TGV As Date 'Thanksgiving Day
Dim CHR As Date 'Christmas Day

NYD = "1/1/2008"
MEM = "5/26/2008"
IND = "7/4/2008"
LAB = "9/1/2008"
TGV = "11/27/2008"
CHR = "12/25/2008"

For i = 1 To intAddDays

Select Case Weekday(dteMyDate) = 1          'Check for Sunday
    dteMyDate = DateAdd("d", 1, dteMyDate)
    Next i
Case Weekday(dteMyDate) = 7                 'Check for Saturday
    dteMyDate = DateAdd("d", 3, dteMyDate)  'Check for Holidays...
    Next i
Case dteMyDate = NYD
    dteMyDate = DateAdd("d", 1, dteMyDate)
    Next i
Case dteMyDate = MEM
    dteMyDate = DateAdd("d", 1, dteMyDate)
    Next i
Case dteMyDate = IND
    dteMyDate = DateAdd("d", 1, dteMyDate)
    Next i
Case dteMyDate = LAB
    dteMyDate = DateAdd("d", 1, dteMyDate)
    Next i
Case dteMyDate = TGV
    dteMyDate = DateAdd("d", 1, dteMyDate)
    Next i
Case dteMyDate = CHR
    dteMyDate = DateAdd("d", 1, dteMyDate)
    Next i
End Select

    dteMyDate = DateAdd("d", 1, dteMyDate)
    Next i

NextBizDay = dteMyDate

End Function

This doesn't seem to work however. The field on the form gives me an error of ?Name as if it cannot find the record source or data.

Matters are a little more complicated in that the value of dteMyDate has to be looked up on a query by using DLookup:

=DLookUp("[DateEntered]","qry_date_targets","[qry_date_targets]![DateRefID]=14")

Combinging these statements gets me

=NextBizDay(DLookup("DateEntered]","qry_date_targets","[qry_date_targets]![DateRefID]=14"),30)

This just gets me a ?Name error again...

 

[gemma-the-husky]

i dont think you can use cases in this way, with the equals sign
then i think you need a function within the case for the holiday

so in pseudocode

weekday = dayofweek(mydate)
(returns 1 to 7) note that access gives constants vbmonday, vbtuesday etc)

now
select case weekday

vbmonday: if holiday(mydate) then add a day
vbtuesday: etc
vbwednesday: etc
vbsaturday: add a day
etc
end select

wrap your loop counter around this in some way, and you should be ok
if you use this a lot, i would read your holiday days into a 366 day (or 31*12) array, and examine this, as this would be quicker than reading a holiday days table repeatedly


--------
further thought - this latter suggestion is still awkward if your dates straddle a year end. Trouble is that holiday days are not really normalised data