Calculating a date using a module

[MarieK]

Need to be able to calculate a future date that leaves out weekends and
holidays. Created a Holiday Lookup Table to help with this. Thanks to the
help of this board, I've gotten close with creating a module...but am
getting error message with Line 7(starts with "[DateofHoliday]="& ) (see
below)...any suggestions??



Public Function PlusWorkdays(dteStart As Date, intNumDays As Long) As Date
PlusWorkdays = dteStart
Do While intNumDays > 0
PlusWorkdays = DateAdd("d", 1, PlusWorkdays)
'IfWeekday(PlusWorkdays, vbMonday)<=5 And
IsNull(DLookup("[Holiday]","HolidayLookUp",_
"[DateofHoliday]="& Format(PlusWorkdays,"\#mm\/dd\/yyyy\#;;;\N\u\l\l")))
Then
'intNumDays=intNumDays - 1
End If
Loop
End Function

 

[Tom Wickerath]

Hi Marie,

Try the WorkingDays2 function (the second one listed) on this page:
http://www.mvps.org/access/datetime/date0006.htm

Alternatively, try the dhCountWorkdaysA function found on this page:
http://www.mvps.org/access/datetime/date0012.htm

Note: if you want to call dhCountWorkdaysA from a query, you must use what
is commonly known as a wrapper function.

Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________