slow query

[Andy_Khosravi]

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.

[Wayne Gillespie]

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

[Lyle Fairfield]

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.

[CDMAPoster]

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

[Andy_Khosravi]

Thanks so much for all the feedback. I'll try and give your ideas a try
today.

[Bob Quintal]

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.

[Peter Sutton]

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

[CDMAPoster]

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.

[CDMAPoster]

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

[CDMAPoster]

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

[CDMAPoster]

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

[CDMAPoster]

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."