skip weekends and holidays on calculated dates??

[samohtwerdna]

Hello again,

I have a few forms and queries that have at least three calculated dates based on a dateSigned field. I had a working date function called dhNextWordayA([dateSigned] + <<calculated amount>>), <<an array of holiday's>>)
This worked just fine except the tediousness of having to re-enter the array of holidays each time I called the function - which was beginning to be a lot. - So then I wrote a RememberHolidays() function that stored an array of holidays - which I couldn't get to work consistantly, but also was lacking easy update. So I wanted to use a table of Holidays that anyone could enter instead of a VBA array.

I found this code:

Public Function AddWorkdays(dteStart As Date, intNumDays As Integer) As Date

Dim dteCurrDate As Date
Dim i As Integer

'intNumDays = Int()
dteCurrDate = dteStart
AddWorkdays = dteStart
i = 1

Do While i < intNumDays
If Weekday(dteCurrDate, vbSunday) <= 5 And _
IsNull(DLookup("[Holiday]", "tblHolidays", "[HoliDate]" = "#" & dteCurrDate & "#")) Then
i = i + 1
End If

dteCurrDate = dteCurrDate + 1

Loop
AddWorkdays = dteCurrDate

Exit_AddWorkDays:
End Function

This code seems to exclude the weekends fine but is not excluding my holidays.

Does anyone have any ideas??

Or thoughts on a better plan of attack???
http://vbaexpress.com/forum/images/smilies/banghead.gif

[OBP]

Have you put in a msgbox DLookup("[Holiday]", "tblHolidays", "[HoliDate]) to check that you are actually getting a value returned?
Another method might be to have the dlookup function as a filter criteria to a query supplying the data to this Vb or a form.

[Cyberdude]

DLOOKUP ... hmmmm ... don't think I've heard of that one.
What's it do?
I wrote a macro that will show the major holidays (including Easter) for any year. Would that be of any help?

[OBP]

The dlookup function allows you to read data from an unopened table.