Workdays Function : Repost

S

SthOzNewbie

As I didn't get a reply posting this in the queries section, I have reposted
in formscoding to see if I have any better luck. Apologies if I have broken
any rules.

I should mention that as well as trying Arvins code I have also used
Klatuu's but got the same slow response from the Pivot Form. Both lots of
code use a table to look up holiday dates which seems to be the common
denominator in slowing the Pivot Table. I'm guessing it's probably not good
practice but is there any way of specifying the holday dates within the code
? Would this speed things up ?

Not sure whether I should be posting here or in forms as the effect of the
issue is felt when refreshing a pivot table form, here goes anyway...

I have a query with a couple of calculated fields which use the the WorkDays
function. I recently changed the expressions to use the WorkDays2 function
which includes provision for holidays (I also had to select Microsoft DAO 3.6
Object Library in References which was not selected previously).

When I ran the query it worked fine and there was no discernable difference
in the time it took to run (produces 104,000 records).

It only became a problem when I refreshed the pivot table form which runs
from this query. With the Workdays function, the pivot table took 1 min 10
sec to refresh. When I changed to the Workdays2 function, the pivot table
took 9 mins 10 sec to run. This seemed like a huge difference so I tested it
a couple more times with the 2 different functions and Workdays2 definately
seems to be the culprit.

Is this difference in processing time reasonable ? Is there something else I
should be changing to optimise this as I really would prefer to account for
holidays. I have pasted the code for both functions below -

'*********** Code Start **************
Public Function WorkingDays(StartDate As Date, EndDate As Date) As Integer
'....................................................................
' Name: WorkingDays
' Inputs: StartDate As Date
' EndDate As Date
' Returns: Integer
' Author: Arvin Meyer
' Date: February 19, 1997
' Comment: Accepts two dates and returns the number of weekdays between them
' Note that this function does not account for holidays.
'....................................................................
On Error GoTo Err_WorkingDays

Dim intCount As Integer

StartDate = StartDate + 1
'If you want to count the day of StartDate as the 1st day
'Comment out the line above

intCount = 0
Do While StartDate <= EndDate
'Make the above < and not <= to not count the EndDate

Select Case Weekday(StartDate)
Case Is = 1, 7
intCount = intCount
Case Is = 2, 3, 4, 5, 6
intCount = intCount + 1
End Select
StartDate = StartDate + 1
Loop
WorkingDays = intCount

Exit_WorkingDays:
Exit Function

Err_WorkingDays:
Select Case Err

Case Else
MsgBox Err.Description
Resume Exit_WorkingDays
End Select

End Function
##################################################
Public Function WorkingDays2(StartDate As Date, EndDate As Date) As Integer
'....................................................................
' Name: WorkingDays2
' Inputs: StartDate As Date
' EndDate As Date
' Returns: Integer
' Author: Arvin Meyer
' Date: May 5,2002
' Comment: Accepts two dates and returns the number of weekdays between them
' Note that this function has been modified to account for holidays. It
requires a table
' named tblHolidays with a field named HolidayDate.
'....................................................................
On Error GoTo Err_WorkingDays2

Dim intCount As Integer
Dim rst As DAO.Recordset
Dim DB As DAO.Database

Set DB = CurrentDb
Set rst = DB.OpenRecordset("SELECT [HolidayDate] FROM tblHolidays",
dbOpenSnapshot)

StartDate = StartDate + 1
'To count StartDate as the 1st day comment out the line above

intCount = 0

Do While StartDate <= EndDate

rst.FindFirst "[HolidayDate] = #" & StartDate & "#"
If Weekday(StartDate) <> vbSunday And Weekday(StartDate) <> vbSaturday Then
If rst.NoMatch Then intCount = intCount + 1
End If

StartDate = StartDate + 1

Loop

WorkingDays2 = intCount

Exit_WorkingDays2:
Exit Function

Err_WorkingDays2:
Select Case Err

Case Else
MsgBox Err.Description
Resume Exit_WorkingDays2
End Select

End Function
 
S

SthOzNewbie

Thanks Tom.

I am using the Workdays function in that link which works fine but does not
allow for holidays. The Workdays2 function does allow for holidays but slows
doen my Pivt form by a factor of nearly 10, I suspect due to the lookup
routine for the holiday dates.

There are some other code samples on the site which look like they may do
the job like SkipHolidays but being a novice with coding I couldn't figure
out how to set up the array for the Holiday dates.

Regards,

IK
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top