Altering an Existing Function

[Roger Converse]

So there is some code lying around in this old database that someone else
created who is nolonger here. Basically, the function takes a look a date in
the past and today's date and determines the number of workdays in between.
What I would like for this funciton to be able to do, is to look back for the
2 previous business days and return those dates into a query.

The function gets called from a query, the field looks as follows:

Days: CountWorkdays2([OrderDate],Now(),1000100111111)-1

The function is pasted below.

Public Function CountWorkdays2(OrderDate As Date, Today As Date, strFlag As
String) As Long
Dim dtCurrent As Date
Dim lngWorkdays As Long
Dim lngCount As Long
Dim lngI As Long
Dim boolSaturdays As Boolean
Dim boolSundays As Boolean

lngWorkdays = 0
lngCount = DateDiff("d", OrderDate, Today) + 1
If lngCount > 0 Then
For lngI = 1 To lngCount
dtCurrent = DateAdd("d", lngI - 1, OrderDate)
If Not boolSaturdays And Not boolSundays Then
If IsWeekday(dtCurrent) Then
If Not IsHoliday(dtCurrent, strFlag) Then
lngWorkdays = lngWorkdays + 1
End If
End If
ElseIf boolSaturdays And Not boolSundays Then
If IsWeekday(dtCurrent) Or IsSaturday(dtCurrent) Then
If Not IsHoliday(dtCurrent, strFlag) Then
lngWorkdays = lngWorkdays + 1
End If
End If
ElseIf Not boolSaturdays And boolSundays Then
If IsWeekday(dtCurrent) Or IsSunday(dtCurrent) Then
If Not IsHoliday(dtCurrent, strFlag) Then
lngWorkdays = lngWorkdays + 1
End If
End If
Else
'All days besides holidays
If Not IsHoliday(dtCurrent, strFlag) Then
lngWorkdays = lngWorkdays + 1
End If
End If
Next lngI
End If
CountWorkdays2 = lngWorkdays
End Function

There are other functions that actually make the decision on whether to
count dtCurrent as a workday, but as opposed to counting workdays, I want to
be able to go back and get the previous 2 workdays.

It would be even better if the number of days weren't restricted, but set
into an array or something, so that I could use this other places as well.
So, a "go get the past 'x' workdays" type thing. Arrays are a little outside
of my scope right now and unfortunately, I am the resident access guru, which
is scary.

Any assistance would be greatly appreciated.

Thank you in advance.
Roger

 

[Dirk Goldgar]

So there is some code lying around in this old database that someone else
created who is nolonger here. Basically, the function takes a look a date
in
the past and today's date and determines the number of workdays in
between.
What I would like for this funciton to be able to do, is to look back for
the
2 previous business days and return those dates into a query.

The function gets called from a query, the field looks as follows:

Days: CountWorkdays2([OrderDate],Now(),1000100111111)-1

The function is pasted below.

Public Function CountWorkdays2(OrderDate As Date, Today As Date, strFlag
As
String) As Long
Dim dtCurrent As Date
Dim lngWorkdays As Long
Dim lngCount As Long
Dim lngI As Long
Dim boolSaturdays As Boolean
Dim boolSundays As Boolean

lngWorkdays = 0
lngCount = DateDiff("d", OrderDate, Today) + 1
If lngCount > 0 Then
For lngI = 1 To lngCount
dtCurrent = DateAdd("d", lngI - 1, OrderDate)
If Not boolSaturdays And Not boolSundays Then
If IsWeekday(dtCurrent) Then
If Not IsHoliday(dtCurrent, strFlag) Then
lngWorkdays = lngWorkdays + 1
End If
End If
ElseIf boolSaturdays And Not boolSundays Then
If IsWeekday(dtCurrent) Or IsSaturday(dtCurrent) Then
If Not IsHoliday(dtCurrent, strFlag) Then
lngWorkdays = lngWorkdays + 1
End If
End If
ElseIf Not boolSaturdays And boolSundays Then
If IsWeekday(dtCurrent) Or IsSunday(dtCurrent) Then
If Not IsHoliday(dtCurrent, strFlag) Then
lngWorkdays = lngWorkdays + 1
End If
End If
Else
'All days besides holidays
If Not IsHoliday(dtCurrent, strFlag) Then
lngWorkdays = lngWorkdays + 1
End If
End If
Next lngI
End If
CountWorkdays2 = lngWorkdays
End Function

There are other functions that actually make the decision on whether to
count dtCurrent as a workday, but as opposed to counting workdays, I want
to
be able to go back and get the previous 2 workdays.

It would be even better if the number of days weren't restricted, but set
into an array or something, so that I could use this other places as well.
So, a "go get the past 'x' workdays" type thing. Arrays are a little
outside
of my scope right now and unfortunately, I am the resident access guru,
which
is scary.

A function can only return one value, so you can't get two dates from it at
once. But you could call the same function twice with two different
arguments, to get each of the two previous work days. There are a number of
workday-math functions here, including one that adds workdays; I think you
could create an alternate version that subtracts workdays:

http://www.mvps.org/access/datetime/date0012.htm