Function Strangeness

[Guest]

I have some code that I use to determine the number of working days between
date1 and date2:

Public Function WorkDays(dtmStart As Date, dtmEnd As Date) As Integer

Dim wkDays As Integer 'this is the counter for the number of days
Dim wkToday As Date ' this is used to increment the date to compare

wkDays = DateDiff("d", dtmStart, dtmEnd) + 1


wkToday = dtmStart
Do Until wkToday > dtmEnd
If WeekDay(wkToday, vbMonday) > 5 Then ' It is a saturday or sunday
wkDays = wkDays - 1 ' for weekend day
ElseIf Not IsNull(DLookup("[Holdate]", "tblHolidays", "[Holdate] =
#" & wkToday & "#")) Then 'It is a holiday
wkDays = wkDays - 1
End If
wkToday = DateAdd("d", 1, wkToday)
Loop

WorkDays = wkDays

End Function

This works great when I have it attached to a form and I pass the variables
to it programatically like this:
Private Sub Command0_Click()
Dim begdate As Date
Dim endDate As Date
begdate = txtBeg.Value
endDate = txtEnd.Value
Text1.Value = basLogic.WorkDays(begdate, endDate)
End Sub
but when I use the function in a query like this:
WorkDays ( [tblTASKsimplified]![act_start_date] ,
[tblTASKsimplified]![act_end_date] )
I get different (wrong) results and it runs extremely slow.

Anyone out there that can help?

 

[Gina Whipp]

Where is the WorkDays code behind a form or in a Module?

 

[Guest]

It is in a module.
--
Michal Joyce
Project Management IS Analyst
Aflac - Project Management Office

Where is the WorkDays code behind a form or in a Module?

I have some code that I use to determine the number of working days between
date1 and date2:

Public Function WorkDays(dtmStart As Date, dtmEnd As Date) As Integer

Dim wkDays As Integer 'this is the counter for the number of days
Dim wkToday As Date ' this is used to increment the date to compare

wkDays = DateDiff("d", dtmStart, dtmEnd) + 1


wkToday = dtmStart
Do Until wkToday > dtmEnd
If WeekDay(wkToday, vbMonday) > 5 Then ' It is a saturday or sunday
wkDays = wkDays - 1 ' for weekend day
ElseIf Not IsNull(DLookup("[Holdate]", "tblHolidays", "[Holdate] =
#" & wkToday & "#")) Then 'It is a holiday
wkDays = wkDays - 1
End If
wkToday = DateAdd("d", 1, wkToday)
Loop

WorkDays = wkDays

End Function

This works great when I have it attached to a form and I pass the
variables
to it programatically like this:
Private Sub Command0_Click()
Dim begdate As Date
Dim endDate As Date
begdate = txtBeg.Value
endDate = txtEnd.Value
Text1.Value = basLogic.WorkDays(begdate, endDate)
End Sub
but when I use the function in a query like this:
WorkDays ( [tblTASKsimplified]![act_start_date] ,
[tblTASKsimplified]![act_end_date] )
I get different (wrong) results and it runs extremely slow.

Anyone out there that can help?

 

[Dirk Goldgar]

I have some code that I use to determine the number of working days
between date1 and date2:

Public Function WorkDays(dtmStart As Date, dtmEnd As Date) As Integer

Dim wkDays As Integer 'this is the counter for the number of days
Dim wkToday As Date ' this is used to increment the date to compare

wkDays = DateDiff("d", dtmStart, dtmEnd) + 1


wkToday = dtmStart
Do Until wkToday > dtmEnd
If WeekDay(wkToday, vbMonday) > 5 Then ' It is a saturday or
sunday wkDays = wkDays - 1 ' for weekend day
ElseIf Not IsNull(DLookup("[Holdate]", "tblHolidays",
"[Holdate] = #" & wkToday & "#")) Then 'It is a holiday
wkDays = wkDays - 1
End If
wkToday = DateAdd("d", 1, wkToday)
Loop

WorkDays = wkDays

End Function

This works great when I have it attached to a form and I pass the
variables to it programatically like this:
Private Sub Command0_Click()
Dim begdate As Date
Dim endDate As Date
begdate = txtBeg.Value
endDate = txtEnd.Value
Text1.Value = basLogic.WorkDays(begdate, endDate)
End Sub
but when I use the function in a query like this:
WorkDays ( [tblTASKsimplified]![act_start_date] ,
[tblTASKsimplified]![act_end_date] )
I get different (wrong) results and it runs extremely slow.

Anyone out there that can help?

It's going to be slow in a query, particularly if you're sorting,
grouping, or applying criteria to that calculated field. But it should
give the correct answer if you call it correctly. I'd expect the
expression to look like this:

WorkDays(tblTASKsimplified.act_start_date,
tblTASKsimplified.act_end_date)

Note in particular the use of dots (.) instead of bangs (!). If that
doesn't work, please post the actual SQL of your query.

 

[Guest]

Changing the bangs to dots did work. I'm not sure I understand why but I'm ok
with that. The other thing I discovered was that one of the dates I was using
was not formatted properly which could have caused some problems. Thanks for
your help on this Dirk. Any recommendations for speeding this thing up? I can
actually watch the query paint each record. It's not so bad at the moment
because I'm only dealing with 29 records but I can't guarantee that in the
long term.

Thanks,

m
--
Michal Joyce
Project Management IS Analyst
Aflac - Project Management Office

I have some code that I use to determine the number of working days
between date1 and date2:

Public Function WorkDays(dtmStart As Date, dtmEnd As Date) As Integer

Dim wkDays As Integer 'this is the counter for the number of days
Dim wkToday As Date ' this is used to increment the date to compare

wkDays = DateDiff("d", dtmStart, dtmEnd) + 1


wkToday = dtmStart
Do Until wkToday > dtmEnd
If WeekDay(wkToday, vbMonday) > 5 Then ' It is a saturday or
sunday wkDays = wkDays - 1 ' for weekend day
ElseIf Not IsNull(DLookup("[Holdate]", "tblHolidays",
"[Holdate] = #" & wkToday & "#")) Then 'It is a holiday
wkDays = wkDays - 1
End If
wkToday = DateAdd("d", 1, wkToday)
Loop

WorkDays = wkDays

End Function

This works great when I have it attached to a form and I pass the
variables to it programatically like this:
Private Sub Command0_Click()
Dim begdate As Date
Dim endDate As Date
begdate = txtBeg.Value
endDate = txtEnd.Value
Text1.Value = basLogic.WorkDays(begdate, endDate)
End Sub
but when I use the function in a query like this:
WorkDays ( [tblTASKsimplified]![act_start_date] ,
[tblTASKsimplified]![act_end_date] )
I get different (wrong) results and it runs extremely slow.

Anyone out there that can help?

It's going to be slow in a query, particularly if you're sorting,
grouping, or applying criteria to that calculated field. But it should
give the correct answer if you call it correctly. I'd expect the
expression to look like this:

WorkDays(tblTASKsimplified.act_start_date,
tblTASKsimplified.act_end_date)

Note in particular the use of dots (.) instead of bangs (!). If that
doesn't work, please post the actual SQL of your query.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

Changing the bangs to dots did work. I'm not sure I understand why
but I'm ok with that. The other thing I discovered was that one of
the dates I was using was not formatted properly which could have
caused some problems. Thanks for your help on this Dirk. Any
recommendations for speeding this thing up? I can actually watch the
query paint each record. It's not so bad at the moment because I'm
only dealing with 29 records but I can't guarantee that in the long
term.

Post the SQL of the query. If there's nothing that really drags the
query down, then it's just the function, and we should focus our
attention there. I believe there are alternate versions that may be
faster. Still, calling an elaborate VBA function in a query can be
slow.

 

[Guest]

Here's the SQL in question.
SELECT tblTaskSimplified.proj_id, tblTaskSimplified.act_start_date,
tblTaskSimplified.act_end_date,
WorkDays(tblTaskSimplified.act_start_date,tblTaskSimplified.act_end_date) AS
Duration
FROM tblTaskSimplified;

Thanks again!

 

[Dirk Goldgar]

Here's the SQL in question.
SELECT tblTaskSimplified.proj_id, tblTaskSimplified.act_start_date,
tblTaskSimplified.act_end_date,
WorkDays(tblTaskSimplified.act_start_date,tblTaskSimplified.act_end_date
)
AS Duration
FROM tblTaskSimplified;

There's nothing there that could slow down the query -- especially if
act_start_date and act_end_date are really date/time fields, not just
text fields that contain date-formatted values -- so the only thing you
can do is try to make the function as efficient as possible. I think
the code posted here:

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

if you can adapt it to your needs, will be faster than the function
you've got. If you aren't taking account of holidays, you can speed
things up a lot by taking out all logic to do with that table. If you
do have to allow for a list of holidays, you might conceivably use a
function that only reads the table once and saves the information in a
static array, returning that array every subsequent time it is called.
That would probably speed things up a lot, but I can't say I've tried
it.

 

[Dirk Goldgar]

WorkDays(tblTaskSimplified.act_start_date,tblTaskSimplified.act_end_date

There's nothing there that could slow down the query -- especially if
act_start_date and act_end_date are really date/time fields, not just
text fields that contain date-formatted values -- so the only thing
you can do is try to make the function as efficient as possible. I
think the code posted here:

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

if you can adapt it to your needs, will be faster than the function
you've got. If you aren't taking account of holidays, you can speed
things up a lot by taking out all logic to do with that table. If you
do have to allow for a list of holidays, you might conceivably use a
function that only reads the table once and saves the information in a
static array, returning that array every subsequent time it is called.
That would probably speed things up a lot, but I can't say I've tried
it.

Actually, it seems that I have done this before and forgot about it. I
created this function:

'----- start of code -----
Function HolidayTableToArray(Optional fReset As Boolean) As Variant

Dim rs As DAO.Recordset
Static adtHolidays() As Date
Static fLoaded As Boolean

If fReset Then
fLoaded = False
End If

If Not fLoaded Then
Erase adtHolidays
Set rs = CurrentDb.OpenRecordset( _
"SELECT HolidayDate FROM tblHolidays", _
dbOpenSnapshot)
With rs
If Not .EOF Then
.MoveLast
.MoveFirst
End If
If .RecordCount > 0 Then
ReDim adtHolidays(.RecordCount - 1)
Do Until .EOF
adtHolidays(.AbsolutePosition) = !HolidayDate
.MoveNext
Loop
End If
.Close
End With
Set rs = Nothing
fLoaded = True
End If

HolidayTableToArray = adtHolidays

End Function
'----- end of code -----

I also modified the code for the dhCountHolidaysA() function from the
module at the link I posted, to make this new function,
dhCountHolidaysB:

'----- start of code -----
Public Function dhCountWorkdaysB( _
ByVal dtmStart As Date, ByVal dtmEnd As Date) _
As Integer

' Count the business days (not counting weekends/holidays) in
' a given date range.

' Modified from code in
' "Visual Basic Language Developer's Handbook"
' by Ken Getz and Mike Gilbert
' Copyright 2000; Sybex, Inc. All rights reserved.
' Modified by Dirk Goldgar, 8/10/2005.

' Requires:
' SkipHolidays
' CountHolidays
' IsWeekend

' In:
' dtmStart:
' Date specifying the start of the range (inclusive)
' dtmEnd:
' Date specifying the end of the range (inclusive)
' (dates will be swapped if out of order)
' adtmDates (Optional):
' Array containing holiday dates. Can also be a single
' date value.
' Out:
' Return Value:
' Number of working days (not counting weekends and
' optionally, holidays) in the specified range.
' Example:
' Debug.Print dhCountWorkdaysA(#7/2/2000#, #7/5/2000#, _
' Array(#1/1/2000#, #7/4/2000#))
'
' returns 2, because 7/2/2000 is Sunday, 7/4/2000 is a holiday,
' leaving 7/3 and 7/5 as workdays.

Dim intDays As Integer
Dim dtmTemp As Date
Dim intSubtract As Integer
Dim adtmDates As Variant

adtmDates = HolidayTableToArray

' Swap the dates if necessary.>
If dtmEnd < dtmStart Then
dtmTemp = dtmStart
dtmStart = dtmEnd
dtmEnd = dtmTemp
End If

' Get the start and end dates to be weekdays.
dtmStart = SkipHolidaysA(adtmDates, dtmStart, 1)
dtmEnd = SkipHolidaysA(adtmDates, dtmEnd, -1)
If dtmStart > dtmEnd Then
' Sorry, no Workdays to be had. Just return 0.
dhCountWorkdaysB = 0
Else
intDays = dtmEnd - dtmStart + 1

' Subtract off weekend days. Do this by figuring out how
' many calendar weeks there are between the dates, and
' multiplying the difference by two (because there are two
' weekend days for each week). That is, if the difference
' is 0, the two days are in the same week. If the
' difference is 1, then we have two weekend days.
intSubtract = (DateDiff("ww", dtmStart, dtmEnd) * 2)

' The answer to our quest is all the weekdays, minus any
' holidays found in the table.
intSubtract = intSubtract + _
CountHolidaysA(adtmDates, dtmStart, dtmEnd)

dhCountWorkdaysB = intDays - intSubtract
End If
End Function
'----- end of code -----