My problem:
I'm having trouble with a query taking much too long to run; a query
without any criteria evaluating only 650 records takes over 300 seconds
to run (over the network. On local drive takes 120 seconds).
The Setup:
I'm running Access 97 non-developer edition. I have exactly zero other
tools to use and no way to change that =(. My database is compiled and
resides on a network drive. The database has not been split into a
front and backend, as I don't have the tools to effectively
redistribute the front end to the more than 350 (occasional) users
every time I make a change. Although the database currently holds only
650 records, it is expected to contain around 10,000 records after it
has been up and running for a few months. (I.E., my problem will get
worse, not better). The results of this query display in a read only
continuous forms format.
I've isolated 98% of the problem down to a counter routine I've called
in the query. This routine tallies and displays the number of days in a
date span minus weekends and company holidays. The purpose is to
display the total age of an 'incident' (one record), the length of time
it has been in its current department, and the length of time its
current owner has 'owned' an incident. With these counters disabled,
the query runs in 5 seconds, (which is perfectly acceptable). I need to
get this query to display all the numbers I need it to in no more than
10 seconds.
The routine itself I shamelessly borrowed from another article as a
workaround (regrettably, I don't remember his name) when I discovered
that the DateDiff function provided with A97 couldn't correctly compute
weekdays only. (Apparently the "w" argument in the DateDiff function of
A97 does not, has not, nor ever will function correctly)
The problematic code is pasted below. I've been looking for ways to
tweak it a bit, and managed to increase performance by 40% by moving
the SET db and RST statements to a separate public function that runs
only once rather than each and every time the loop runs. It is still
too slow though.
This code iterates one time for every day between two date spans and
increments a counter if the date it is currently evaluating is not a
weekend or company holiday.
Public Function srcWorkingDays2(StartDate As Date, EndDate As Date) As
Double
Dim intCount As Double
Dim RST As Recordset
Dim db As Database
Set db = CurrentDb
Set RST = db.OpenRecordset("SELECT [HolidayDate] FROM tblHolidays",
dbOpenSnapshot)
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
End If
StartDate = StartDate + 1
Loop
srcWorkingDays2 = intCount
End Function
This is an example of one of the expressions in the query that calls
this function:
TOTDaysOpen:
srcworkingdays2(Format$([currentTOTdate],"mm/dd/yyyy"),IIf(IsNull([dateclosed]),Date(),Format$([dateclosed],"mm/dd/yyyy")))
A big problem area in the VB part of the code seems to be the FindFirst
action. Commenting out just the Holidays part of the calculation
slashes query times by 60%. I've also noticed that Access seems to be
running the query four complete times every time a user hits the search
button on my search-input screen. This appears to be some internal Jet
workings, and I have no idea how to put a stop to it. If that could be
stopped that would slash query times by another 75%.
However, even with both of those improvements the query time would
still be well over the 10-second mark. What I'm thinking I need is some
sort of paging on the continuous form that displays the results. If I
could limit this so that it only calculated on 12 records at a time the
query time would be much better.
I haven't the foggiest idea how I might go about setting that up.
Any tips or suggestions any of you may have would be much appreciated. 11 3109
On 28 Dec 2005 15:04:11 -0800, An***********@bcbsmn.com wrote: However, even with both of those improvements the query time would still be well over the 10-second mark. What I'm thinking I need is some sort of paging on the continuous form that displays the results. If I could limit this so that it only calculated on 12 records at a time the query time would be much better.
I haven't the foggiest idea how I might go about setting that up.
Any tips or suggestions any of you may have would be much appreciated.
Take your srcWorkingDays2 function out of the query all together and call it from a text box in the continuous form.
Assuming you have textboxes in the continuous form called StartDate and EndDate, add a textbox and as it's control
source put -
=srcWorkingDays2([StartDate],[EndDate])
The function will only be called for records currently displayed in the continuous form. You may however get a slight
delay as each record is evaluated but this usually insignificant.
Wayne Gillespie
Gosford NSW Australia
My suggestion is that in a couple of sentences you describe exactly
what it is that you want to do without reference to any code but
describing your tables carefully and ask for suggestions as to how to
do it. An***********@bcbsmn.com wrote: My problem: I'm having trouble with a query taking much too long to run; a query without any criteria evaluating only 650 records takes over 300 seconds to run (over the network. On local drive takes 120 seconds).
The Setup: I'm running Access 97 non-developer edition. I have exactly zero other tools to use and no way to change that =(. My database is compiled and resides on a network drive. The database has not been split into a front and backend, as I don't have the tools to effectively redistribute the front end to the more than 350 (occasional) users every time I make a change. Although the database currently holds only 650 records, it is expected to contain around 10,000 records after it has been up and running for a few months. (I.E., my problem will get worse, not better). The results of this query display in a read only continuous forms format.
I've isolated 98% of the problem down to a counter routine I've called in the query. This routine tallies and displays the number of days in a date span minus weekends and company holidays. The purpose is to display the total age of an 'incident' (one record), the length of time it has been in its current department, and the length of time its current owner has 'owned' an incident. With these counters disabled, the query runs in 5 seconds, (which is perfectly acceptable). I need to get this query to display all the numbers I need it to in no more than 10 seconds.
The routine itself I shamelessly borrowed from another article as a workaround (regrettably, I don't remember his name) when I discovered that the DateDiff function provided with A97 couldn't correctly compute weekdays only. (Apparently the "w" argument in the DateDiff function of A97 does not, has not, nor ever will function correctly)
The problematic code is pasted below. I've been looking for ways to tweak it a bit, and managed to increase performance by 40% by moving the SET db and RST statements to a separate public function that runs only once rather than each and every time the loop runs. It is still too slow though.
This code iterates one time for every day between two date spans and increments a counter if the date it is currently evaluating is not a weekend or company holiday.
Public Function srcWorkingDays2(StartDate As Date, EndDate As Date) As Double Dim intCount As Double Dim RST As Recordset Dim db As Database
Set db = CurrentDb Set RST = db.OpenRecordset("SELECT [HolidayDate] FROM tblHolidays", dbOpenSnapshot)
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 End If
StartDate = StartDate + 1
Loop
srcWorkingDays2 = intCount End Function
This is an example of one of the expressions in the query that calls this function: TOTDaysOpen: srcworkingdays2(Format$([currentTOTdate],"mm/dd/yyyy"),IIf(IsNull([dateclosed]),Date(),Format$([dateclosed],"mm/dd/yyyy")))
A big problem area in the VB part of the code seems to be the FindFirst action. Commenting out just the Holidays part of the calculation slashes query times by 60%. I've also noticed that Access seems to be running the query four complete times every time a user hits the search button on my search-input screen. This appears to be some internal Jet workings, and I have no idea how to put a stop to it. If that could be stopped that would slash query times by another 75%.
However, even with both of those improvements the query time would still be well over the 10-second mark. What I'm thinking I need is some sort of paging on the continuous form that displays the results. If I could limit this so that it only calculated on 12 records at a time the query time would be much better.
I haven't the foggiest idea how I might go about setting that up.
Any tips or suggestions any of you may have would be much appreciated.
After reflection it seems there should be a way to count the weekend
days and holidays without going through each date individually. That
reflection caused me to think of:
SELECT IncidentID, [EndDate] - [StartDate] + 1 - (SELECT
Count(HolidayDate) FROM tblHolidays WHERE (HolidayDate BETWEEN
[StartDate] AND [EndDate]) AND Weekday(HolidayDate) <> 1 AND
Weekday(HolidayDate) <> 7) - CountWeekendDays([StartDate], [EndDate])
AS WorkingDays FROM tblIncidents;
That is, (the number of workdays) = (the number of days between
StartDate and EndDate inclusive) - (holidays that are not on a weekend)
- (weekend days). That leaves the CountWeekendDays function to do.
'Start Module Code---
Public Function CountWeekendDays(dtStart As Date, dtEnd As Date)
Dim intSat As Integer
Dim intSun As Integer
'This function assumes dtStart <= dtEnd
CountWeekendDays = 0
intSat = (LEDay(dtEnd, 7) - GEDay(dtStart, 7)) / 7 + 1
intSun = (LEDay(dtEnd, 1) - GEDay(dtStart, 1)) / 7 + 1
CountWeekendDays = (intSat + intSun + Abs(intSat) + Abs(intSun)) / 2
End Function
Public Function LEDay(dtX As Date, vbDay As Integer) As Date
LEDay = DateAdd("d", -(7 + WeekDay(dtX) - vbDay) Mod 7, dtX)
End Function
Public Function GEDay(dtX As Date, vbDay As Integer) As Date
GEDay = DateAdd("d", (7 + vbDay - WeekDay(dtX)) Mod 7, dtX)
End Function
'End Module Code-----
Note: I whipped this up rather quickly with almost no testing so be
sure to test it and verify the logic before using. LEDay finds the
previous vbDay on or before the given date. Also note that the
firstdayofweek parameter of the Weekday function can be used instead as
an aid to get the previous or following weekend day. (X + Abs(X)) / 2
just gives X when X is positive and 0 otherwise. The IIf or Sgn
functions could have worked for this purpose also. The '<> 1' and '<>
7' can use '> 1' and '< 7' in the SQL statement instead. I'm going to
have to change some other code I have soon for calculating workdays so
that large date ranges don't cause a performance issue since I
implemented it by stepping through dates. I personally don't use a
holiday table so I'm going to have to rethink the best way to handle
that situation as well. I thought Wayne's idea was excellent.
James A. Fortune CD********@FortuneJames.com
Thanks so much for all the feedback. I'll try and give your ideas a try
today. CD********@FortuneJames.com wrote in
news:11*********************@z14g2000cwz.googlegro ups.com: After reflection it seems there should be a way to count the weekend days and holidays without going through each date individually. That reflection caused me to think of:
SELECT IncidentID, [EndDate] - [StartDate] + 1 - (SELECT Count(HolidayDate) FROM tblHolidays WHERE (HolidayDate BETWEEN [StartDate] AND [EndDate]) AND Weekday(HolidayDate) <> 1 AND Weekday(HolidayDate) <> 7) - CountWeekendDays([StartDate], [EndDate]) AS WorkingDays FROM tblIncidents;
That is, (the number of workdays) = (the number of days between StartDate and EndDate inclusive) - (holidays that are not on a weekend) - (weekend days). That leaves the CountWeekendDays function to do.
'Start Module Code--- Public Function CountWeekendDays(dtStart As Date, dtEnd As Date) Dim intSat As Integer Dim intSun As Integer
'This function assumes dtStart <= dtEnd CountWeekendDays = 0 intSat = (LEDay(dtEnd, 7) - GEDay(dtStart, 7)) / 7 + 1 intSun = (LEDay(dtEnd, 1) - GEDay(dtStart, 1)) / 7 + 1 CountWeekendDays = (intSat + intSun + Abs(intSat) + Abs(intSun)) / 2 End Function
Public Function LEDay(dtX As Date, vbDay As Integer) As Date LEDay = DateAdd("d", -(7 + WeekDay(dtX) - vbDay) Mod 7, dtX) End Function
Public Function GEDay(dtX As Date, vbDay As Integer) As Date GEDay = DateAdd("d", (7 + vbDay - WeekDay(dtX)) Mod 7, dtX) End Function 'End Module Code-----
Note: I whipped this up rather quickly with almost no testing so be sure to test it and verify the logic before using. LEDay finds the previous vbDay on or before the given date. Also note that the firstdayofweek parameter of the Weekday function can be used instead as an aid to get the previous or following weekend day. (X + Abs(X)) / 2 just gives X when X is positive and 0 otherwise. The IIf or Sgn functions could have worked for this purpose also. The '<> 1' and '<> 7' can use '> 1' and '< 7' in the SQL statement instead. I'm going to have to change some other code I have soon for calculating workdays so that large date ranges don't cause a performance issue since I implemented it by stepping through dates. I personally don't use a holiday table so I'm going to have to rethink the best way to handle that situation as well. I thought Wayne's idea was excellent.
James A. Fortune CD********@FortuneJames.com
Interesting technique.
Years ago I used a different calendar table (in foxBase Plus as
a time reference). The
table had three fields: Workdate, daynum, isholiday. Daynum
incremented on workdays, but did not increment on weekends or
holidays, so daynum of enddate minus daynum of startdate.plus 1
gave the number of working days between the two dates. The plus
one comes from the fact that the start of startdate is start of
workday and end of enddate is end of workday, YMMV
It would be fast in Access although a little wasteful of
diskspace.
--
Bob Quintal
PA is y I've altered my email address.
On Wed, 28 Dec 2005 23:51:51 GMT, Wayne Gillespie
<be*****@NOhotmailSPAM.com.au> wrote: On 28 Dec 2005 15:04:11 -0800, An***********@bcbsmn.com wrote:
However, even with both of those improvements the query time would still be well over the 10-second mark. What I'm thinking I need is some sort of paging on the continuous form that displays the results. If I could limit this so that it only calculated on 12 records at a time the query time would be much better.
I haven't the foggiest idea how I might go about setting that up.
Any tips or suggestions any of you may have would be much appreciated.
Take your srcWorkingDays2 function out of the query all together and call it from a text box in the continuous form.
Assuming you have textboxes in the continuous form called StartDate and EndDate, add a textbox and as it's control source put - =srcWorkingDays2([StartDate],[EndDate])
The function will only be called for records currently displayed in the continuous form. You may however get a slight delay as each record is evaluated but this usually insignificant.
Wayne Gillespie Gosford NSW Australia
And if I may tack on...
While Wayne's suggestion will speed up things considerably, you might
squeeze a bit more performance if you do your count of holidays
another way. Because, assuming there are less holidays than the total
days in each case, you could open a recordset based on all holidays
between the open and end of each case, and loop through these,
counting the number of found instances.
P CD********@FortuneJames.com wrote: I'm going to have to change some other code I have soon for calculating workdays so that large date ranges don't cause a performance issue since I implemented it by stepping through dates. I personally don't use a holiday table so I'm going to have to rethink the best way to handle that situation as well. I thought Wayne's idea was excellent.
In order to implement this I need to change my holiday functions so
that the input is any date and the output is the date of that holiday
for that year. Let h(dtX) represent the holiday date returned for a
typical holiday during Year(dtX). To count the number of times a
holiday occurs within a date range, only the behavior near the start
and end dates of the range matter since any whole year will contain
exactly one of that holiday. If the year of the start date differs
from the year of the end date then the count of holidays between dt1
and dt2 is simply Abs(h(dt1) >= dt1) + Abs(h(dt2) <= dt2) + Year(dt2) -
Year(dt1) - 1, subject to final verification and testing of the logic.
When Year(dt1) = Year(dt2), h(dt1) = h(dt2) and the first two terms
cannot both be False when dt1 < dt2 so the same expression seems to
work for dt1 and dt2 being in the same year. I will verify all the
logic of this before presenting a final solution. The conversion of
the holiday functions to return a date doesn't look difficult and
should also be more useful to those who need to create holiday tables.
James A. Fortune CD********@FortuneJames.com
I was ftp'ing aroung for free software, preferably with source code.
Back then most universities and companies had one or two mainframes
connected to the internet. Suddenly, at harvard.edu there was what
looked like a roomful of computers available for anonymous ftp. Almost
all of them had the word "mac" as part of the name. PC's hooked up to
the internet? They're opening a whole new world. CD********@FortuneJames.com wrote: In order to implement this I need to change my holiday functions so that the input is any date and the output is the date of that holiday for that year.
Here are the preliminary changes to the holiday functions subject to
verification and testing:
'Begin modDirectDateFunctions
Option Compare Database
Option Explicit
Public Function GetChristmas(dtInYear As Date) As Date
GetChristmas = DateSerial(Year(dtInYear), 12, 25)
End Function
Public Function GetColumbus(dtInYear As Date) As Date
'Second Monday in October
GetColumbus = DateSerial(Year(dtInYear), 10, NthXDay(2, vbMonday,
DateSerial(Year(dtInYear), 10, 1)))
End Function
Public Function GetEaster(dtInYear As Date) As Date
Dim M As Integer
Dim d As Integer
Dim y As Integer
Dim DT As Date
M = 24
y = Year(dtInYear)
d = (19 * (y Mod 19) + M) Mod 30
DT = DateAdd("d", d, DateSerial(y, 3, 22))
DT = DT + (8 - WeekDay(DT)) Mod 7
GetEaster = DateSerial(Year(dtInYear), Month(DT), Day(DT))
End Function
Public Function GetIndependence(dtInYear As Date) As Date
GetIndependence = DateSerial(Year(dtInYear), 7, 4)
End Function
Public Function GetLabor(dtInYear As Date) As Date
'First Monday in September
GetLabor = DateSerial(Year(dtInYear), 9, NthXDay(1, vbMonday,
DateSerial(Year(dtInYear), 9, 1)))
End Function
Public Function GetMartinLutherKing(dtInYear As Date) As Date
'Third Monday in January
GetMartinLutherKing = DateSerial(Year(dtInYear), 1, NthXDay(3,
vbMonday, DateSerial(Year(dtInYear), 1, 1)))
End Function
Public Function GetMemorial(dtInYear As Date) As Date
'Last Monday in May
GetMemorial = DateSerial(Year(dtInYear), 5,
Day(LastXDay(DateSerial(Year(dtInYear), 5, 1), vbMonday)))
End Function
Public Function GetNewYears(dtInYear As Date) As Date
GetNewYears = DateSerial(Year(dtInYear), 1, 1)
End Function
Public Function GetPresidents(dtInYear As Date) As Date
'Third Monday in February
GetPresidents = DateSerial(Year(dtInYear), 2, NthXDay(3, vbMonday,
DateSerial(Year(dtInYear), 2, 1)))
End Function
Public Function GetThanksgiving(dtInYear As Date) As Date
'Fourth Thursday in November
GetThanksgiving = DateSerial(Year(dtInYear), 11, NthXDay(4, vbThursday,
DateSerial(Year(dtInYear), 11, 1)))
End Function
Public Function GetVeterans(dtInYear As Date) As Date
GetVeterans = DateSerial(Year(dtInYear), 11, 11)
End Function
'These functions are required for many of the functions above
Public Function LastXDay(dtD As Date, DayConst As Integer) As Date
LastXDay = DateSerial(Year(dtD), Month(dtD) + 1,
(-WeekDay(DateSerial(Year(dtD), Month(dtD) + 1, 0)) + DayConst - 7) Mod
7)
End Function
Public Function NthXDay(N As Integer, d As Integer, dtD As Date) As
Integer
NthXDay = (7 - WeekDay(DateSerial(Year(dtD), Month(dtD), 1)) + d) Mod 7
+ 1 + (N - 1) * 7
End Function
'End modDirectDateFunctions
Sample call:
GetEaster(#1/1/06#) => 4/16/06
The holiday functions are listed in alphabetical order. The NthXDay
function requires a date within the target month so I had to
manufacture one for several of the holiday functions.
James A. Fortune CD********@FortuneJames.com CD********@FortuneJames.com wrote: CD********@FortuneJames.com wrote:
In order to implement this I need to change my holiday functions so that the input is any date and the output is the date of that holiday for that year.
Here are the preliminary changes to the holiday functions subject to verification and testing:
'Begin modDirectDateFunctions Option Compare Database Option Explicit
Public Function GetChristmas(dtInYear As Date) As Date GetChristmas = DateSerial(Year(dtInYear), 12, 25) End Function
Public Function GetColumbus(dtInYear As Date) As Date 'Second Monday in October GetColumbus = DateSerial(Year(dtInYear), 10, NthXDay(2, vbMonday, DateSerial(Year(dtInYear), 10, 1))) End Function
Public Function GetEaster(dtInYear As Date) As Date Dim M As Integer Dim d As Integer Dim y As Integer Dim DT As Date
M = 24 y = Year(dtInYear) d = (19 * (y Mod 19) + M) Mod 30 DT = DateAdd("d", d, DateSerial(y, 3, 22)) DT = DT + (8 - WeekDay(DT)) Mod 7 GetEaster = DateSerial(Year(dtInYear), Month(DT), Day(DT)) End Function
Public Function GetIndependence(dtInYear As Date) As Date GetIndependence = DateSerial(Year(dtInYear), 7, 4) End Function
Public Function GetLabor(dtInYear As Date) As Date 'First Monday in September GetLabor = DateSerial(Year(dtInYear), 9, NthXDay(1, vbMonday, DateSerial(Year(dtInYear), 9, 1))) End Function
Public Function GetMartinLutherKing(dtInYear As Date) As Date 'Third Monday in January GetMartinLutherKing = DateSerial(Year(dtInYear), 1, NthXDay(3, vbMonday, DateSerial(Year(dtInYear), 1, 1))) End Function
Public Function GetMemorial(dtInYear As Date) As Date 'Last Monday in May GetMemorial = DateSerial(Year(dtInYear), 5, Day(LastXDay(DateSerial(Year(dtInYear), 5, 1), vbMonday))) End Function
Public Function GetNewYears(dtInYear As Date) As Date GetNewYears = DateSerial(Year(dtInYear), 1, 1) End Function
Public Function GetPresidents(dtInYear As Date) As Date 'Third Monday in February GetPresidents = DateSerial(Year(dtInYear), 2, NthXDay(3, vbMonday, DateSerial(Year(dtInYear), 2, 1))) End Function
Public Function GetThanksgiving(dtInYear As Date) As Date 'Fourth Thursday in November GetThanksgiving = DateSerial(Year(dtInYear), 11, NthXDay(4, vbThursday, DateSerial(Year(dtInYear), 11, 1))) End Function
Public Function GetVeterans(dtInYear As Date) As Date GetVeterans = DateSerial(Year(dtInYear), 11, 11) End Function
'These functions are required for many of the functions above Public Function LastXDay(dtD As Date, DayConst As Integer) As Date LastXDay = DateSerial(Year(dtD), Month(dtD) + 1, (-WeekDay(DateSerial(Year(dtD), Month(dtD) + 1, 0)) + DayConst - 7) Mod 7) End Function
Public Function NthXDay(N As Integer, d As Integer, dtD As Date) As Integer NthXDay = (7 - WeekDay(DateSerial(Year(dtD), Month(dtD), 1)) + d) Mod 7 + 1 + (N - 1) * 7 End Function 'End modDirectDateFunctions
Sample call: GetEaster(#1/1/06#) => 4/16/06
The holiday functions are listed in alphabetical order. The NthXDay function requires a date within the target month so I had to manufacture one for several of the holiday functions.
James A. Fortune CD********@FortuneJames.com
This is still not the final solution since I haven't verified the
logic. I have done some testing without finding any mistakes so far.
Version 1:
Public Function CountHolidays(dtStart As Date, dtEnd As Date)
Dim lngTemp As Long
lngTemp = lngTemp + Abs(GetNewYears(dtStart) >= dtStart) +
Abs(GetNewYears(dtEnd) <= dtEnd) + Year(dtEnd) - Year(dtStart) - 1
lngTemp = lngTemp + Abs(GetMartinLutherKing(dtStart) >= dtStart) +
Abs(GetMartinLutherKing(dtEnd) <= dtEnd) + Year(dtEnd) - Year(dtStart)
- 1
lngTemp = lngTemp + Abs(GetPresidents(dtStart) >= dtStart) +
Abs(GetPresidents(dtEnd) <= dtEnd) + Year(dtEnd) - Year(dtStart) - 1
lngTemp = lngTemp + Abs(GetEaster(dtStart) >= dtStart) +
Abs(GetEaster(dtEnd) <= dtEnd) + Year(dtEnd) - Year(dtStart) - 1
lngTemp = lngTemp + Abs(GetMemorial(dtStart) >= dtStart) +
Abs(GetMemorial(dtEnd) <= dtEnd) + Year(dtEnd) - Year(dtStart) - 1
lngTemp = lngTemp + Abs(GetIndependence(dtStart) >= dtStart) +
Abs(GetIndependence(dtEnd) <= dtEnd) + Year(dtEnd) - Year(dtStart) - 1
lngTemp = lngTemp + Abs(GetLabor(dtStart) >= dtStart) +
Abs(GetLabor(dtEnd) <= dtEnd) + Year(dtEnd) - Year(dtStart) - 1
lngTemp = lngTemp + Abs(GetColumbus(dtStart) >= dtStart) +
Abs(GetColumbus(dtEnd) <= dtEnd) + Year(dtEnd) - Year(dtStart) - 1
lngTemp = lngTemp + Abs(GetVeterans(dtStart) >= dtStart) +
Abs(GetVeterans(dtEnd) <= dtEnd) + Year(dtEnd) - Year(dtStart) - 1
lngTemp = lngTemp + Abs(GetThanksgiving(dtStart) >= dtStart) +
Abs(GetThanksgiving(dtEnd) <= dtEnd) + Year(dtEnd) - Year(dtStart) - 1
lngTemp = lngTemp + Abs(GetChristmas(dtStart) >= dtStart) +
Abs(GetChristmas(dtEnd) <= dtEnd) + Year(dtEnd) - Year(dtStart) - 1
CountHolidays = lngTemp
End Function
Version 2:
Public Function CountHolidays(dtStart As Date, dtEnd As Date)
Dim intNumberOfHolidays As Integer
Dim lngTemp As Long
intNumberOfHolidays = 11
lngTemp = (Year(dtEnd) - Year(dtStart) - 1) * intNumberOfHolidays
lngTemp = lngTemp + Abs(GetNewYears(dtStart) >= dtStart) +
Abs(GetNewYears(dtEnd) <= dtEnd)
lngTemp = lngTemp + Abs(GetMartinLutherKing(dtStart) >= dtStart) +
Abs(GetMartinLutherKing(dtEnd) <= dtEnd)
lngTemp = lngTemp + Abs(GetPresidents(dtStart) >= dtStart) +
Abs(GetPresidents(dtEnd) <= dtEnd)
lngTemp = lngTemp + Abs(GetEaster(dtStart) >= dtStart) +
Abs(GetEaster(dtEnd) <= dtEnd)
lngTemp = lngTemp + Abs(GetMemorial(dtStart) >= dtStart) +
Abs(GetMemorial(dtEnd) <= dtEnd)
lngTemp = lngTemp + Abs(GetIndependence(dtStart) >= dtStart) +
Abs(GetIndependence(dtEnd) <= dtEnd)
lngTemp = lngTemp + Abs(GetLabor(dtStart) >= dtStart) +
Abs(GetLabor(dtEnd) <= dtEnd)
lngTemp = lngTemp + Abs(GetColumbus(dtStart) >= dtStart) +
Abs(GetColumbus(dtEnd) <= dtEnd)
lngTemp = lngTemp + Abs(GetVeterans(dtStart) >= dtStart) +
Abs(GetVeterans(dtEnd) <= dtEnd)
lngTemp = lngTemp + Abs(GetThanksgiving(dtStart) >= dtStart) +
Abs(GetThanksgiving(dtEnd) <= dtEnd)
lngTemp = lngTemp + Abs(GetChristmas(dtStart) >= dtStart) +
Abs(GetChristmas(dtEnd) <= dtEnd)
CountHolidays = lngTemp
End Function
Version 2 is slightly faster but the user needs to remember to change
intNumberOfHolidays when commenting out unwanted holidays. Version 1
can be used by commenting out unwanted holidays directly.
This function assumes the existence of the functions contained in the
quoted post. It gets more efficient compared to other methods as the
number of years in the interval increases. I.e., it gets sweeter as
the years go by :-). It still runs quickly for short ranges.
Actually, it should take about the same time to run regardless of the
number of years in the interval. Mind the line wrap. The code still
does not account for holidays that are observed on Friday or Monday
when they fall on a weekend. When I'm finished, if I use a holiday
table it will have everything except holidays in it :-).
James A. Fortune CD********@FortuneJames.com CD********@FortuneJames.com wrote: The code still does not account for holidays that are observed on Friday or Monday when they fall on a weekend.
'Some untested functions to fix that problem
'Holidays that always fall on a Monday:
'Columbus' Day
'Labor Day
'Martin Luther King Day
'Memorial Day
'President's Day
'Holidays that always fall on a Thursday:
'Thanksgiving Day
'want something like:
'If Weekday(dtX) = 7 Then dtX = DateAdd("d", -1, dtX)
'If Weekday(dtX) = 1 Then dtX = DateAdd("d", 1, dtX)
'Holidays that always fall on a Sunday:
Public Function GetEasterMonday(dtInYear As Date) As Date
Dim M As Integer
Dim d As Integer
Dim y As Integer
Dim DT As Date
M = 24
y = Year(dtInYear)
d = (19 * (y Mod 19) + M) Mod 30
DT = DateAdd("d", d, DateSerial(y, 3, 22))
DT = DT + (8 - WeekDay(DT)) Mod 7
GetEasterMonday = DateAdd("d", 1, DateSerial(Year(dtInYear), Month(DT),
Day(DT)))
End Function
'Holidays that can fall on a Saturday or on a Sunday:
Public Function GetIndependenceObserved(dtInYear As Date) As Date
Dim dtTemp As Date
dtTemp = DateSerial(Year(dtInYear), 7, 4)
If Weekday(dtTemp) = 7 Then dtTemp = DateAdd("d", -1, dtTemp)
If Weekday(dtTemp) = 1 Then dtTemp = DateAdd("d", 1, dtTemp)
GetIndependenceObserved = dtTemp
End Function
Public Function GetChristmasObserved(dtInYear As Date) As Date
Dim dtTemp As Date
dtTemp = DateSerial(Year(dtInYear), 12, 25)
If Weekday(dtTemp) = 7 Then dtTemp = DateAdd("d", -1, dtTemp)
If Weekday(dtTemp) = 1 Then dtTemp = DateAdd("d", 1, dtTemp)
GetChristmasObserved = dtTemp
End Function
Public Function GetNewYearsObserved(dtInYear As Date) As Date
Dim dtTemp As Date
dtTemp = DateSerial(Year(dtInYear), 1, 1)
If Weekday(dtTemp) = 7 Then dtTemp = DateAdd("d", -1, dtTemp)
If Weekday(dtTemp) = 1 Then dtTemp = DateAdd("d", 1, dtTemp)
GetNewYearsObserved = dtTemp
End Function
Public Function GetVeteransObserved(dtInYear As Date) As Date
Dim dtTemp As Date
dtTemp = DateSerial(Year(dtInYear), 11, 11)
If Weekday(dtTemp) = 7 Then dtTemp = DateAdd("d", -1, dtTemp)
If Weekday(dtTemp) = 1 Then dtTemp = DateAdd("d", 1, dtTemp)
GetVeteransObserved = dtTemp
End Function
James A. Fortune CD********@FortuneJames.com CD********@FortuneJames.com wrote: Public Function GetNewYearsObserved(dtInYear As Date) As Date Dim dtTemp As Date
dtTemp = DateSerial(Year(dtInYear), 1, 1) If Weekday(dtTemp) = 7 Then dtTemp = DateAdd("d", -1, dtTemp) If Weekday(dtTemp) = 1 Then dtTemp = DateAdd("d", 1, dtTemp) GetNewYearsObserved = dtTemp End Function
The function GetNewYearsObserved has been changed so that the observed
date is always in the same year as Jan 1. I found that out from an old
MS Foxpro KB article.
Public Function GetNewYearsObserved(dtInYear As Date) As Date
Dim dtTemp As Date
dtTemp = DateSerial(Year(dtInYear), 1, 1)
If WeekDay(dtTemp) = 7 Then dtTemp = DateAdd("d", 2, dtTemp)
If WeekDay(dtTemp) = 1 Then dtTemp = DateAdd("d", 1, dtTemp)
GetNewYearsObserved = dtTemp
End Function
The function CountHolidays has been changed by adding a line
initializing lngTemp = 0 and changing some of the holidays to the
observed version:
Public Function CountHolidays(dtStart As Date, dtEnd As Date)
Dim lngTemp As Long
lngTemp = 0
lngTemp = lngTemp + Abs(GetNewYearsObserved(dtStart) >= dtStart) +
Abs(GetNewYearsObserved(dtEnd) <= dtEnd) + Year(dtEnd) - Year(dtStart)
- 1
lngTemp = lngTemp + Abs(GetMartinLutherKing(dtStart) >= dtStart) +
Abs(GetMartinLutherKing(dtEnd) <= dtEnd) + Year(dtEnd) - Year(dtStart)
- 1
lngTemp = lngTemp + Abs(GetPresidents(dtStart) >= dtStart) +
Abs(GetPresidents(dtEnd) <= dtEnd) + Year(dtEnd) - Year(dtStart) - 1
lngTemp = lngTemp + Abs(GetEasterMonday(dtStart) >= dtStart) +
Abs(GetEasterMonday(dtEnd) <= dtEnd) + Year(dtEnd) - Year(dtStart) - 1
lngTemp = lngTemp + Abs(GetMemorial(dtStart) >= dtStart) +
Abs(GetMemorial(dtEnd) <= dtEnd) + Year(dtEnd) - Year(dtStart) - 1
lngTemp = lngTemp + Abs(GetIndependenceObserved(dtStart) >= dtStart) +
Abs(GetIndependenceObserved(dtEnd) <= dtEnd) + Year(dtEnd) -
Year(dtStart) - 1
lngTemp = lngTemp + Abs(GetLabor(dtStart) >= dtStart) +
Abs(GetLabor(dtEnd) <= dtEnd) + Year(dtEnd) - Year(dtStart) - 1
lngTemp = lngTemp + Abs(GetColumbus(dtStart) >= dtStart) +
Abs(GetColumbus(dtEnd) <= dtEnd) + Year(dtEnd) - Year(dtStart) - 1
lngTemp = lngTemp + Abs(GetVeteransObserved(dtStart) >= dtStart) +
Abs(GetVeteransObserved(dtEnd) <= dtEnd) + Year(dtEnd) - Year(dtStart)
- 1
lngTemp = lngTemp + Abs(GetThanksgiving(dtStart) >= dtStart) +
Abs(GetThanksgiving(dtEnd) <= dtEnd) + Year(dtEnd) - Year(dtStart) - 1
lngTemp = lngTemp + Abs(GetChristmasObserved(dtStart) >= dtStart) +
Abs(GetChristmasObserved(dtEnd) <= dtEnd) + Year(dtEnd) - Year(dtStart)
- 1
If lngTemp < 0 Then lngTemp = 0
CountHolidays = lngTemp
End Function
The fact that New Year's is always observed during the same year as
January 1 actually helps keep the logic intact. The 'If lngTemp < 0
Then lngTemp = 0' is in case someone puts in the dates backwards. The
logic is correct when dtStart and dtEnd fall in different years. That
leaves dtStart and dtEnd during the same year. Instead of trying to
come up with a situation that doesn't work I simply did enough testing
for that situation to convince myself that it will produce the correct
number. The final monkey wrench is that sometimes holidays get shifted
by executive order, say Christmas falling on a Thursday being observed
on Friday by mandate. I'm just going to ignore that one.
The next thing I might do with these is to adjust either my
DateIntersection function or my ProrateMonth function to look only at
overlapping business days and to count only business days in the total
number of days in the month. Even if these functions aren't used to
calculate business days without using a holiday table, the functions
should be useful for creating holiday tables. When testing, be sure to
remember whether the Observed or the Actual holiday date is being used.
I'll put a module combining the pieces of code I posted into a zipped
A97 mdb at the usual location when I get around to doing it. The
CountHolidays function is 16 lines (including the blank line) when the
line wrap is taken out.
James A. Fortune CD********@FortuneJames.com
On the Capacity Report form that uses holiday functions the users
checked the holidays they wanted under "Exclude these holidays." I'm
going to flip the label and the code so that it says, "Include these
holidays." This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Shay |
last post by:
essentially I am trying to do some counts based on some
assumptions in the recordset. So I get the RS back, put
the values into a variable, move to the next record in the
RS and compare what is in...
|
by: Tim Fountain |
last post by:
We've recently enabled slow query logging on a server and it's proving
interesting seeing which queries are bogging things down. This one is
puzzling me a little:
SELECT articleid, type,...
|
by: Thomas Bartkus |
last post by:
Is it possible that the elapsed time being measured includes waiting for the
client to acknowledge that it has received all the data? In *addition* to
the server execution time?
Documentation...
|
by: AG |
last post by:
I am able to run a query which runs FAst in QA but slow in the
application.It takes about 16 m in QA but 1000 ms on the
Application.What I wanted to know is why would the query take a long
time...
|
by: Yonatan Goraly |
last post by:
I am in the process of adding PostgreSQL support for an application, in
addition to Oracle and MS SQL.
I am using PostgreSQL version 7.3.2, Red Hat 9.0 on Intel Pentium III board.
I have a...
|
by: Janross |
last post by:
I'm having trouble with a query that's prohibitively slow. On my free-standing
office computer it's fine (well, 2-4 seconds), but on the client's network, it
takes at least 5 minutes to run. ...
|
by: Jennyfer J Barco |
last post by:
In my application I have a datagrid. The code calls a Stored procedure and
brings like 200 records. I created a dataset and then a dataview to bind the
results of the query to my grid using ...
|
by: Dave Hammond |
last post by:
Hi All,
I'm trying to use the slow-query-log (with
--log-queries-not-using-indexes enabled) to determine if any queries
need optimization, and have a few questions about some entries I'm...
|
by: Emin |
last post by:
Dear Experts,
I have a fairly simple query in which adding a where clause slows
things down by at least a factor of 100. The following is the slow
version of the query
...
|
by: existential.philosophy |
last post by:
This is a new problem for me: I have some queries that open very
slowly in design view.
My benchmark query takes about 20 minutes to open in design view. That
same query takes about 20 minutes...
|
by: MeoLessi9 |
last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
|
by: DolphinDB |
last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation.
Take...
|
by: DolphinDB |
last post by:
Tired of spending countless mintues downsampling your data? Look no further!
In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
|
by: Aftab Ahmad |
last post by:
Hello Experts!
I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
|
by: Aftab Ahmad |
last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below.
Dim IE As Object
Set IE =...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: marcoviolo |
last post by:
Dear all,
I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...
|
by: PapaRatzi |
last post by:
Hello,
I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
| |