G
Guest
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
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