By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,719 Members | 1,875 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,719 IT Pros & Developers. It's quick & easy.

Function to count workdays

P: n/a
I have an A97 module called modWorkdayFunctions in:

http://www.oakland.edu/~fortune/WorkdayFunctions.zip

It allows the counting of workdays taking into consideration up to 11
U.S. holidays. More holidays can be added easily. Also, there is a
boolean flag for including or excluding Saturdays or Sundays as part of
the work week. The only thing I know of that is missing is that some
holidays are observed on a Friday or a Monday if they fall on a
weekend. These functions do not account for that yet.

Sample strFlag = "11111111111" -- 1 = check that holiday, 0 = don't
check

James A. Fortune

Nov 13 '05 #1
Share this Question
Share on Google+
18 Replies


P: n/a
On 10 Aug 2005 16:41:38 -0700, ji********@compumarc.com wrote:
I have an A97 module called modWorkdayFunctions in:

[snip]

Store business data in tables, not in code.

Nov 13 '05 #2

P: n/a
mi**@sherrillshelton.net wrote:
On 10 Aug 2005 16:41:38 -0700, ji********@compumarc.com wrote:
I have an A97 module called modWorkdayFunctions in:

[snip]

Store business data in tables, not in code.


The code I've seen so far (from Getz, et. al.) uses code plus a holiday
table. I'm just getting rid of the holiday table. If you'd rather
maintain that table, be my guest. Or are you referring to something
else?

James A. Fortune

At the time of the Louisiana Purchase, trade to England accounted for
about 75% of U.S. trade and trade to France accounted for about 25% of
U.S. Trade -- Jefferson's Gambit

Nov 13 '05 #3

P: n/a
ji********@compumarc.com wrote in
news:11*********************@g14g2000cwa.googlegro ups.com:
mi**@sherrillshelton.net wrote:
Store business data in tables, not in code.


The code I've seen so far (from Getz, et. al.) uses code plus
a holiday table. I'm just getting rid of the holiday table.
If you'd rather maintain that table, be my guest. Or are you
referring to something else?

Would you prefer the end users to maintain the code or a table?
Holidays can change and some jurisdictions have additional holidays
from other jurisdictions. It is trivial to alter a table, but not
trivial in a distributed .mde for example.

Or is this an attempt at job security?

--
Bob Quintal

PA is y I've altered my email address.
Nov 13 '05 #4

P: n/a
On 11 Aug 2005 06:27:10 -0700, ji********@compumarc.com wrote:
mi**@sherrillshelton.net wrote:
On 10 Aug 2005 16:41:38 -0700, ji********@compumarc.com wrote:
>I have an A97 module called modWorkdayFunctions in: [snip]

Store business data in tables, not in code.


The code I've seen so far (from Getz, et. al.) uses code plus a holiday
table. I'm just getting rid of the holiday table.


I understand what you're doing. I also understand why getting rid of
the table is a really bad idea.
If you'd rather
maintain that table, be my guest.


If I have a choice between having people maintain data and having
people maintain code, I'd rather have people maintain data. It's
cheaper, and it's more reliable.

FWIW, I use a calendar table, not a holiday table. If a holiday table
omits, say, "25-Apr-2038", you can't tell by looking whether that date
is a deliberately omitted ordinary day, or a mistakenly omitted
holiday. In my calendar table, that date is a holiday, Easter. I
might be wrong, but there's no doubt what I entered.

Some people work on holidays, and some people don't work on days that
are neither holidays nor weekends. "Workday" and "holiday" are really
meaningless in software design--the careful designer knows you always
have to ask, "For whom?"

BTW, your code won't compile here. I'll leave it to you to figure out
why.

Nov 13 '05 #5

P: n/a
Bob Quintal wrote:
ji********@compumarc.com wrote in
news:11*********************@g14g2000cwa.googlegro ups.com:
mi**@sherrillshelton.net wrote:
Store business data in tables, not in code.


The code I've seen so far (from Getz, et. al.) uses code plus
a holiday table. I'm just getting rid of the holiday table.
If you'd rather maintain that table, be my guest. Or are you
referring to something else?

Would you prefer the end users to maintain the code or a table?
Holidays can change and some jurisdictions have additional holidays
from other jurisdictions. It is trivial to alter a table, but not
trivial in a distributed .mde for example.

Or is this an attempt at job security?


Not at all. I have a form where the user checks which holidays they
want checked and whether Saturdays or Sundays are to be included in the
work week. My goal is that once the code is finished and tested there
won't be any code to maintain because it won't change. It works quite
well for what I am doing. I agree that others may have special
circumstances that force them to rely on a holiday table.

James A. Fortune

Nov 13 '05 #6

P: n/a
m...@sherrillshelton.net wrote:
On 11 Aug 2005 06:27:10 -0700, ji********@compumarc.com wrote:
mi**@sherrillshelton.net wrote:
On 10 Aug 2005 16:41:38 -0700, ji********@compumarc.com wrote:

>I have an A97 module called modWorkdayFunctions in:
[snip]

Store business data in tables, not in code.
The code I've seen so far (from Getz, et. al.) uses code plus a holiday
table. I'm just getting rid of the holiday table.


I understand what you're doing. I also understand why getting rid of
the table is a really bad idea.
If you'd rather
maintain that table, be my guest.


If I have a choice between having people maintain data and having
people maintain code, I'd rather have people maintain data. It's
cheaper, and it's more reliable.

FWIW, I use a calendar table, not a holiday table. If a holiday table
omits, say, "25-Apr-2038", you can't tell by looking whether that date
is a deliberately omitted ordinary day, or a mistakenly omitted
holiday. In my calendar table, that date is a holiday, Easter. I
might be wrong, but there's no doubt what I entered.


You have a valid point about people accidentally picking the wrong
holiday. Maybe I should look into this. I default the holidays to the
ones listed in the employee handbook. I think even a holiday table
that has Y/N fields for which holidays to check that year would be
better than the table you're suggesting. After all, if computers can
calculate holidays why look them up yourself?
Some people work on holidays, and some people don't work on days that
are neither holidays nor weekends. "Workday" and "holiday" are really
meaningless in software design--the careful designer knows you always
have to ask, "For whom?"

BTW, your code won't compile here. I'll leave it to you to figure out
why.


It's moot because you've said you don't like the idea. It compiles
fine in the U.S. on A97 through A2K3 AFAIK. My application was
designed mostly for capacity forcasting in the U.S. Work days are days
that not everyone has off. It works great for that. I think it would
not be difficult at all to adapt it to most holidays elsewhere with
some exceptions. If you don't like it don't use it, but your argument
about how maintaining holiday tables is better seems backwards to me,
especially if I end up tightening up the user error weakness as you
suggest.

James A. Fortune

Nov 13 '05 #7

P: n/a
On 17 Aug 2005 16:07:14 -0700, ji********@compumarc.com wrote:
I think even a holiday table
that has Y/N fields for which holidays to check that year would be
better than the table you're suggesting.
It might be, if the only questions you can imagine asking of a table
are "Is this a work day for me?" I imagined a *lot* more questions.

OTOH, if you use a holiday table, you can probably use Getz's code
unaltered. Then you don't have to write any code, and you can spend
some time writing a test scaffold for Getz's code. (Or whoever's code
you're using.) An Access developer who writes tests before writing
code is a rare bird. All too rare.
After all, if computers can
calculate holidays why look them up yourself?
What computers can do and what programmers can do are different things.
Off the top of my head . . .

Calculations can be wrong in ways that are not obvious to programmers.
Data in a table can be seen to be right.

Loading data into a table makes certain kinds of errors utterly
obvious. It's also dead simple to report or extract data that's in a
table for testing.

Even correct calculations--and not all yours are correct--can't be
indexed, so their performance in some queries is worse than dismal.

Boundary cases take a little work to get right in code. But data in a
table is simple data-entry work.

Tables scale well, and they're not tied to a particular dbms. (But
implementing all the constraints on any table, including calendar
tables, is very dependent on the target dbms.) Ordinary users don't
need INSERT or DELETE permissions. In most cases, ordinary users don't
even need UPDATE permissions.

Code that calculates work days has trouble coping with uncommon events,
like terrorist attacks, utility failures, worker strikes, and natural
disasters. Calendar tables handle these kinds of things easily.
Some people work on holidays, and some people don't work on days that
are neither holidays nor weekends. "Workday" and "holiday" are really
meaningless in software design--the careful designer knows you always
have to ask, "For whom?"

BTW, your code won't compile here. I'll leave it to you to figure out
why.


It's moot because you've said you don't like the idea. It compiles
fine in the U.S. on A97 through A2K3 AFAIK.


It's not moot if you intend to distribute the code. I'm in the US. I
have A97 through A2K3. Your code doesn't compile here. The mistake
you made is common to programmers who don't have a lot of experience in
Microsoft products. Thinking it's a moot point is an attitude common
to programmers who don't have a lot of experience.
My application was
designed mostly for capacity forcasting in the U.S. Work days are days
that not everyone has off. It works great for that. I think it would
not be difficult at all to adapt it to most holidays elsewhere with
some exceptions.
Calendar tables can handle all holidays everywhere with no exceptions.
If you don't like it don't use it, but your argument
about how maintaining holiday tables is better seems backwards to me,
Suppose the federal government makes August 12 a national holiday
beginning in 2008. Let's call it "Stupid Interface Day". Updating a
calendar table is so simple I won't even go into it. How do we update
your code?

Well, we need to add a function, "IsStupidInterface()". That's pretty
easy. And we also need to add a case to IsHoliday(). Is your problem
obvious to you yet?
especially if I end up tightening up the user error weakness as you
suggest.


I just gave you that one to get you started. You have a lot more
problems ahead of you.

--
Mike Sherrill

Nov 13 '05 #8

P: n/a
mi**@sherrillshelton.net wrote:
On 17 Aug 2005 16:07:14 -0700, ji********@compumarc.com wrote:
I think even a holiday table
that has Y/N fields for which holidays to check that year would be
better than the table you're suggesting.
It might be, if the only questions you can imagine asking of a table
are "Is this a work day for me?" I imagined a *lot* more questions.

OTOH, if you use a holiday table, you can probably use Getz's code
unaltered. Then you don't have to write any code, and you can spend
some time writing a test scaffold for Getz's code. (Or whoever's code
you're using.) An Access developer who writes tests before writing
code is a rare bird. All too rare.


I found out what happened with the code. I did write a test for the
code using an IsEaster2 function. I deleted the IsEaster function and
replaced it with the IsEaster2 function then changed the name to
IsEaster. I missed one of the references which accounts for the
compile error you got. When the 2 is removed it compiles fine. I
could add a piece of code that looks for dates in a table beyond the
dates that are checked for holidays. Would that satisfy your
objections?
After all, if computers can
calculate holidays why look them up yourself?
What computers can do and what programmers can do are different things.
Off the top of my head . . .

Calculations can be wrong in ways that are not obvious to programmers.
Data in a table can be seen to be right.

Loading data into a table makes certain kinds of errors utterly
obvious. It's also dead simple to report or extract data that's in a
table for testing.

Even correct calculations--and not all yours are correct--can't be
indexed, so their performance in some queries is worse than dismal.


I would be interested in seeing which calculations are not correct.
Plus the code ran fairly quickly.

Boundary cases take a little work to get right in code. But data in a
table is simple data-entry work.

Tables scale well, and they're not tied to a particular dbms. (But
implementing all the constraints on any table, including calendar
tables, is very dependent on the target dbms.) Ordinary users don't
need INSERT or DELETE permissions. In most cases, ordinary users don't
even need UPDATE permissions.
Tables indeed scale better to different dbms. That's the best point
you've made so far. Anyone using the code I posted should keep this
point in mind.

Code that calculates work days has trouble coping with uncommon events,
like terrorist attacks, utility failures, worker strikes, and natural
disasters. Calendar tables handle these kinds of things easily.
Good point. My function is mainly for the future rather than the past.
Go ahead and add a table lookup to my code :-).
Some people work on holidays, and some people don't work on days that
are neither holidays nor weekends. "Workday" and "holiday" are really
meaningless in software design--the careful designer knows you always
have to ask, "For whom?"

BTW, your code won't compile here. I'll leave it to you to figure out
why.
It's moot because you've said you don't like the idea. It compiles
fine in the U.S. on A97 through A2K3 AFAIK.


It's not moot if you intend to distribute the code. I'm in the US. I
have A97 through A2K3. Your code doesn't compile here. The mistake
you made is common to programmers who don't have a lot of experience in
Microsoft products. Thinking it's a moot point is an attitude common
to programmers who don't have a lot of experience.


I suspect that I've solved more problems than you have.
My application was
designed mostly for capacity forcasting in the U.S. Work days are days
that not everyone has off. It works great for that. I think it would
not be difficult at all to adapt it to most holidays elsewhere with
some exceptions.
Calendar tables can handle all holidays everywhere with no exceptions.


If you don't like it don't use it, but your argument about how
maintaining holiday tables is better seems backwards to me (for the
holidays).
If you don't like it don't use it, but your argument
about how maintaining holiday tables is better seems backwards to me,
Suppose the federal government makes August 12 a national holiday
beginning in 2008. Let's call it "Stupid Interface Day". Updating a
calendar table is so simple I won't even go into it. How do we update
your code?

Well, we need to add a function, "IsStupidInterface()". That's pretty
easy. And we also need to add a case to IsHoliday(). Is your problem
obvious to you yet?


It would take about half an hour including changing the user interface.
What's the problem?
especially if I end up tightening up the user error weakness as you
suggest.
I just gave you that one to get you started. You have a lot more
problems ahead of you.


No. The code's nearly finished. A table lookup to handle strange
cases and the holiday on a weekend case.

--
Mike Sherrill


James A. Fortune

Nov 13 '05 #9

P: n/a
mi**@sherrillshelton.net wrote:
On 17 Aug 2005 16:07:14 -0700, ji********@compumarc.com wrote:
Tables scale well, and they're not tied to a particular dbms. (But
implementing all the constraints on any table, including calendar
tables, is very dependent on the target dbms.) Ordinary users don't
need INSERT or DELETE permissions. In most cases, ordinary users don't
even need UPDATE permissions.
I had an interesting thought. I could rewrite the holiday functions I
have so that a SQL Server or other dbms user could run an Access query
that spits out the dates of all the holidays that fall within a range
of years for input into their holiday table.
Mike Sherrill


James A. Fortune

Nov 13 '05 #10

P: n/a
On 19 Aug 2005 10:55:14 -0700, ji********@compumarc.com wrote:
I had an interesting thought. I could rewrite the holiday functions I
have so that a SQL Server or other dbms user could run an Access query
that spits out the dates of all the holidays that fall within a range
of years for input into their holiday table.


You could do that. How do I know your results are right?

--
Mike Sherrill

Nov 13 '05 #11

P: n/a
On 19 Aug 2005 10:20:21 -0700, ji********@compumarc.com wrote:

[snip]
I found out what happened with the code. I did write a test for the
code using an IsEaster2 function. I deleted the IsEaster function and
replaced it with the IsEaster2 function then changed the name to
IsEaster. I missed one of the references which accounts for the
compile error you got.
That's half the story. The problem, in case you've forgotten, is that
I got a compile error, but you didn't. Did you solve *that* problem?
I
could add a piece of code that looks for dates in a table beyond the
dates that are checked for holidays. Would that satisfy your
objections?
My first recommendation was "Store business data in tables, not in
code." It still applies. Storing part of your business data in tables
and part of it in code is almost as bad as storing all of it in code.
Maybe worse.

[snip]
Even correct calculations--and not all yours are correct--can't be
indexed, so their performance in some queries is worse than dismal.


I would be interested in seeing which calculations are not correct.
Plus the code ran fairly quickly.


If you can write good tests, you should find the problems pretty
quickly. IMHO, if you can't figure out how to test code, you shouldn't
release it.

[snip]Tables indeed scale better to different dbms. That's the best point
you've made so far. Anyone using the code I posted should keep this
point in mind.
Oh, I think "Store business data in tables, not in code" is the best
point so far.
Code that calculates work days has trouble coping with uncommon events,
like terrorist attacks, utility failures, worker strikes, and natural
disasters. Calendar tables handle these kinds of things easily.


Good point. My function is mainly for the future rather than the past.


That might be what you intended. It's not what you coded.

[snip]
It's not moot if you intend to distribute the code. I'm in the US. I
have A97 through A2K3. Your code doesn't compile here. The mistake
you made is common to programmers who don't have a lot of experience in
Microsoft products. Thinking it's a moot point is an attitude common
to programmers who don't have a lot of experience.


I suspect that I've solved more problems than you have.


You might have. But I seem to have more experience with *this* problem
than you do.

[snip]
Suppose the federal government makes August 12 a national holiday
beginning in 2008. Let's call it "Stupid Interface Day". Updating a
calendar table is so simple I won't even go into it. How do we update
your code?

Well, we need to add a function, "IsStupidInterface()". That's pretty
easy. And we also need to add a case to IsHoliday(). Is your problem
obvious to you yet?


It would take about half an hour including changing the user interface.
What's the problem?


The problem seems to be that you didn't actually try to *do* it. Run
that change through a full software lifecycle and tell us how how you
get on.

[snip]No. The code's nearly finished. A table lookup to handle strange
cases and the holiday on a weekend case.


I thought it was finished when you posted it and started this thread.

--
Mike Sherrill

Nov 13 '05 #12

P: n/a
mi**@sherrillshelton.net wrote:
On 19 Aug 2005 10:20:21 -0700, ji********@compumarc.com wrote:

[snip]
I found out what happened with the code. I did write a test for the
code using an IsEaster2 function. I deleted the IsEaster function and
replaced it with the IsEaster2 function then changed the name to
IsEaster. I missed one of the references which accounts for the
compile error you got.
That's half the story. The problem, in case you've forgotten, is that
I got a compile error, but you didn't. Did you solve *that* problem?


I changed the file and uploaded it. Removing a single '2' was all that
was required for those that do not want to download it again. I
apologize for not recompiling when I replaced the IsEaster function.
I'm usually more careful.
I
could add a piece of code that looks for dates in a table beyond the
dates that are checked for holidays. Would that satisfy your
objections?
My first recommendation was "Store business data in tables, not in
code." It still applies. Storing part of your business data in tables
and part of it in code is almost as bad as storing all of it in code.
Maybe worse.


I see what you mean by business data now. I prefer handling holidays
separately. It's your choice.

[snip]
Even correct calculations--and not all yours are correct--can't be
indexed, so their performance in some queries is worse than dismal.
I would be interested in seeing which calculations are not correct.
Plus the code ran fairly quickly.


If you can write good tests, you should find the problems pretty
quickly. IMHO, if you can't figure out how to test code, you shouldn't
release it.


Yes, a simple compile would have found the problem. The reason for not
doing a compile was that the code is being used daily and the
replacement IsEaster function, which ironically came about from months
of testing and verification, should have been a simple deletion of the
'2.' I did run tests on every function but I'll check them again.

[snip]
Tables indeed scale better to different dbms. That's the best point
you've made so far. Anyone using the code I posted should keep this
point in mind.
Oh, I think "Store business data in tables, not in code" is the best
point so far.


Err.., the tables don't run themselves. Getz' solution required code
also.
Code that calculates work days has trouble coping with uncommon events,
like terrorist attacks, utility failures, worker strikes, and natural
disasters. Calendar tables handle these kinds of things easily.
Good point. My function is mainly for the future rather than the past.


That might be what you intended. It's not what you coded.


What I mean is that the application I use the functions in is looking
at future capacity, so terrorist attacks, strikes and other exceptions
did not have to be dealt with. This point you made is what convinced
me that a table to handle exceptions that is not calculated would be
useful.

[snip]
It's not moot if you intend to distribute the code. I'm in the US. I
have A97 through A2K3. Your code doesn't compile here. The mistake
you made is common to programmers who don't have a lot of experience in
Microsoft products. Thinking it's a moot point is an attitude common
to programmers who don't have a lot of experience.
I suspect that I've solved more problems than you have.


You might have. But I seem to have more experience with *this* problem
than you do.


O.K.

[snip]
Suppose the federal government makes August 12 a national holiday
beginning in 2008. Let's call it "Stupid Interface Day". Updating a
calendar table is so simple I won't even go into it. How do we update
your code?

Well, we need to add a function, "IsStupidInterface()". That's pretty
easy. And we also need to add a case to IsHoliday(). Is your problem
obvious to you yet?
It would take about half an hour including changing the user interface.
What's the problem?


The problem seems to be that you didn't actually try to *do* it. Run
that change through a full software lifecycle and tell us how how you
get on.


Do you mean using automated testing software?

[snip]
No. The code's nearly finished. A table lookup to handle strange
cases and the holiday on a weekend case.
I thought it was finished when you posted it and started this thread.


Sorry. It's a work in progress.

--
Mike Sherrill


James A. Fortune

Nov 13 '05 #13

P: n/a
On 22 Aug 2005 11:33:27 -0700, ji********@compumarc.com wrote:

[snip]
That's half the story. The problem, in case you've forgotten, is that
I got a compile error, but you didn't. Did you solve *that* problem?
I changed the file and uploaded it.


There's your problem. Your release procedure doesn't require you to
even compile code before moving it to production, let alone test it.
I'm usually more careful.
You might be. We don't know "usually"; we just know what you posted.
My first recommendation was "Store business data in tables, not in
code." It still applies. Storing part of your business data in tables
and part of it in code is almost as bad as storing all of it in code.
Maybe worse.


I see what you mean by business data now. I prefer handling holidays
separately.


Everyone has preferences, but not all preferences are created equal.

*Why* do you prefer handling holidays separately? What advantage do
you see to calculating some of the days when some people don't work?

[snip]
If you can write good tests, you should find the problems pretty
quickly. IMHO, if you can't figure out how to test code, you shouldn't
release it.


Yes, a simple compile would have found the problem.


But you didn't do a simple compile.

And although compiling finds most syntactic mistakes, but it doesn't
find any other kind. That's why experienced programmers write tests.
(In Access, compiling might not even find syntactic mistakes. Think
"conditional compilation" and "corrupt project" for two examples.)
The reason for not
doing a compile was that the code is being used daily and the
replacement IsEaster function, which ironically came about from months
of testing and verification, should have been a simple deletion of the
'2.'
There's no irony here.

The odds are that you didn't compile your code because you believed
"This change won't break anything." No programmer with more than a
week of experience believes that.

Researching and recording the dates of Easter in a table would not
likely take months, even if you assigned the job to a high-school
freshman and asked for all the Easters for the next 500 years. Is it
really a wise use of time and money?

[snip]
Oh, I think "Store business data in tables, not in code" is the best
point so far.


Err.., the tables don't run themselves. Getz' solution required code
also.


Yes. But I don't use a holiday table. I use calendar tables. Getz
didn't calculate holidays either.

Simple SQL statements do all the counting. Sometimes I need the result
in code. But the code that runs a query and extracts the only value in
the only row it returns is simple and commonplace. The system is
internally consistent--every day is treated the same; it's just a row
in a table. It's completely transparent; even noobs can see what's
going on, and tell immediately if something's wrong.

Calendar tables are useful in ways I didn't have to anticipate. The
last question I got was, "Can I get a report of all the months that
have five Fridays?"

You seem to think you have some significant insight that more
experienced programmers lack. I'd like to know what it is.
(Experienced programmers don't give much weight to "preference".)
>> Code that calculates work days has trouble coping with uncommon events,
>> like terrorist attacks, utility failures, worker strikes, and natural
>> disasters. Calendar tables handle these kinds of things easily.
>
>Good point. My function is mainly for the future rather than the past.


That might be what you intended. It's not what you coded.


What I mean is that the application I use the functions in is looking
at future capacity, so terrorist attacks, strikes and other exceptions
did not have to be dealt with.


Your application *might* know that, but your code doesn't. (And it
doesn't work that way, either.)
This point you made is what convinced
me that a table to handle exceptions that is not calculated would be
useful.


Why? What are you *really* interested in? Your MDB file is named
"WorkdayFunctions.mdb". Your module is "modWorkdayFunctions". The
first two public functions are CountWeekdays() and CountWorkdays().
Aren't you really interested in working days? There's nothing
exceptional about a day some people don't work. It's all just business
data.

[snip]
>> Suppose the federal government makes August 12 a national holiday
>> beginning in 2008. Let's call it "Stupid Interface Day". Updating a
>> calendar table is so simple I won't even go into it. How do we update
>> your code?
>>
>> Well, we need to add a function, "IsStupidInterface()". That's pretty
>> easy. And we also need to add a case to IsHoliday(). Is your problem
>> obvious to you yet?
>
>It would take about half an hour including changing the user interface.
>What's the problem?


The problem seems to be that you didn't actually try to *do* it. Run
that change through a full software lifecycle and tell us how how you
get on.


Do you mean using automated testing software?


No, I mean make the change, move it to production, distribute it to all
the sites that use it, and follow it into maintenance. That should be
a real eye-opener.

--
Mike Sherrill

Nov 13 '05 #14

P: n/a
mi**@sherrillshelton.net wrote:
On 22 Aug 2005 11:33:27 -0700, ji********@compumarc.com wrote:

[snip]
That's half the story. The problem, in case you've forgotten, is that
I got a compile error, but you didn't. Did you solve *that* problem?
I changed the file and uploaded it.


There's your problem. Your release procedure doesn't require you to
even compile code before moving it to production, let alone test it.


That's not a bug. It's a feature :-).
I'm usually more careful.
You might be. We don't know "usually"; we just know what you posted.


I accept your criticism. What is your suggestion for testing?
My first recommendation was "Store business data in tables, not in
code." It still applies. Storing part of your business data in tables
and part of it in code is almost as bad as storing all of it in code.
Maybe worse.
I see what you mean by business data now. I prefer handling holidays
separately.


Everyone has preferences, but not all preferences are created equal.

*Why* do you prefer handling holidays separately? What advantage do
you see to calculating some of the days when some people don't work?


I thought it was silly to go looking for the dates of the holidays.
Around here company holidays are days when no one works.
If you can write good tests, you should find the problems pretty
quickly. IMHO, if you can't figure out how to test code, you shouldn't
release it.


Yes, a simple compile would have found the problem.


But you didn't do a simple compile.

And although compiling finds most syntactic mistakes, but it doesn't
find any other kind. That's why experienced programmers write tests.
(In Access, compiling might not even find syntactic mistakes. Think
"conditional compilation" and "corrupt project" for two examples.)


What kinds of tests are you proposing? In Mathematics and Engineering
there is a lot of art in determining how to know if something is right
or not. Nevertheless, even the greatest mathematicians of all time
missed the bad assumptions that lead to the introduction of
non-euclidian geometry.
The reason for not
doing a compile was that the code is being used daily and the
replacement IsEaster function, which ironically came about from months
of testing and verification, should have been a simple deletion of the
'2.'
There's no irony here.

The odds are that you didn't compile your code because you believed
"This change won't break anything." No programmer with more than a
week of experience believes that.


Yes, I should have compiled it. Did I say that already?

Researching and recording the dates of Easter in a table would not
likely take months, even if you assigned the job to a high-school
freshman and asked for all the Easters for the next 500 years. Is it
really a wise use of time and money?
I think it would take them months. How would they know they got the
right dates? I will continue to work on and develop this code
regardless of how you feel I am spending my time.
Simple SQL statements do all the counting. Sometimes I need the result
in code. But the code that runs a query and extracts the only value in
the only row it returns is simple and commonplace. The system is
internally consistent--every day is treated the same; it's just a row
in a table. It's completely transparent; even noobs can see what's
going on, and tell immediately if something's wrong.
With holiday code the noobs don't even get a chance to make a mistake.
Calendar tables are useful in ways I didn't have to anticipate. The
last question I got was, "Can I get a report of all the months that
have five Fridays?"
Yeah, I can see all kinds of unexpected benefits :-). If I add the
table part for exceptions, how is that any different in providing the
same unexpected benefits?

You seem to think you have some significant insight that more
experienced programmers lack. I'd like to know what it is.
(Experienced programmers don't give much weight to "preference".)
I was just being polite. I really believe what I'm doing is an
improvement. No one who posts here is so good that everything they do
is accepted without question. I read one of Getz' books. I have no
doubt that his code works fine. Would he want me to be simply happy
with what he wrote or encourage me to try to improve things? You can
disagree about whether or not you think what I did is an improvement
but I already like it better than maintaining holiday dates.
Do you mean using automated testing software?


No, I mean make the change, move it to production, distribute it to all
the sites that use it, and follow it into maintenance. That should be
a real eye-opener.


Now going through a full development cycle with code that I'm giving
away seems like more of a waste of time and money, although it would be
very educational.

--
Mike Sherrill


Thanks for your comments. Although some of your remarks seem
illogically hostile I believe they are helping me shape the code into
something that can be of use to many. My main intent was not to try to
please everyone, but to provide the holiday functions for whatever uses
people came up with.

James A. Fortune

To get a Ph. D. you have to one-up yourself continually. -- Dr. Scott
Burgett

Nov 13 '05 #15

P: n/a
ji********@compumarc.com wrote:
What kinds of tests are you proposing? In Mathematics and Engineering
there is a lot of art in determining how to know if something is right
or not. Nevertheless, even the greatest mathematicians of all time
missed the bad assumptions that lead to the introduction of
non-euclidian geometry.


That's non-euclidean. I guess my testing process should have included
a spell check :-).

James A. Fortune

Nov 13 '05 #16

P: n/a
On 24 Aug 2005 10:47:54 -0700, ji********@compumarc.com wrote:

[snippage throughout]
I accept your criticism. What is your suggestion for testing?
It's not rocket science. Think about it.
Everyone has preferences, but not all preferences are created equal.

*Why* do you prefer handling holidays separately? What advantage do
you see to calculating some of the days when some people don't work?

I thought it was silly to go looking for the dates of the holidays.
Around here company holidays are days when no one works.


When you post code for use by everyone, you need to consider more
general cases. Almost everyone on the planet doesn't work where you
do.

In the general case, a holiday and a day off work are two different
things. And, in the general case, both of them have limited scope.
(That is, holidays and days off apply to particular individuals,
groups, or organizations. Neither apply to everyone.)

[snip]What kinds of tests are you proposing?
Tests that demonstrate your code does what it promises to do.
Researching and recording the dates of Easter in a table would not
likely take months, even if you assigned the job to a high-school
freshman and asked for all the Easters for the next 500 years. Is it
really a wise use of time and money?


I think it would take them months. How would they know they got the
right dates?


That's exactly my question for you. You post code that promises to
accurately calculate all these dates. How do we know you got it
right?
Simple SQL statements do all the counting. Sometimes I need the result
in code. But the code that runs a query and extracts the only value in
the only row it returns is simple and commonplace. The system is
internally consistent--every day is treated the same; it's just a row
in a table. It's completely transparent; even noobs can see what's
going on, and tell immediately if something's wrong.


With holiday code the noobs don't even get a chance to make a mistake.


I think that you're talking about end users. I was
talking about admins--the people who have to maintain the system.
If I add the
table part for exceptions, how is that any different in providing the
same unexpected benefits?
In the first place, if *you* "add the table part", I'd expect you to
get it, well, not quite right. I base my expectation on what you've
posted so far. In the second place, the people who maintain systems
are interested in making their own lives easier, not harder. "Add the
table part", and these people have to deal with three different ways
of doing essentially one thing (boolean flags for weekends, an
unreadable string of ones and zeroes for holidays, and a "table part".)
You seem to think you have some significant insight that more
experienced programmers lack. I'd like to know what it is.
(Experienced programmers don't give much weight to "preference".)


I was just being polite. I really believe what I'm doing is an
improvement.


I was just being polite, too. What evidence do you offer for your
belief, besides "I thought it was silly", and personal preference?
No one who posts here is so good that everything they do
is accepted without question.
I agree. But not every criticism carries equal weight. Your
criticism of Getz's approach--code plus a table of holidays--seems to
boil down to "I think it's silly". There are some people on Usenet
who can stop me dead in my tracks by saying that, but you're not one
of them. Your "improvement" replaces his table with a brittle string
of ones and zeroes, two Boolean flags, and possibly another table. It
doesn't make sense.
You can
disagree about whether or not you think what I did is an improvement
but I already like it better than maintaining holiday dates.


I'm sure you do. But you haven't tried to follow it through the
software lifecycle. Think through what happens to the string of
holiday flags when you add a holiday. Better yet, think through what
happens to programs that use your code when you add a holiday.

--
Mike Sherrill

Nov 13 '05 #17

P: n/a
mi**@sherrillshelton.net wrote:
On 24 Aug 2005 10:47:54 -0700, ji********@compumarc.com wrote:

[snippage throughout] When you post code for use by everyone, you need to consider more
general cases. Almost everyone on the planet doesn't work where you
do.

In the general case, a holiday and a day off work are two different
things. And, in the general case, both of them have limited scope.
(That is, holidays and days off apply to particular individuals,
groups, or organizations. Neither apply to everyone.)
I think holidays are general enough to post some code. If you need to
know the days off for particular individuals then I would not recommend
using my code for that.
[snip]
Does this indicate that you're omitting some of the post or just being
rude?
What kinds of tests are you proposing?


Tests that demonstrate your code does what it promises to do.


I will make it a point to perform more thorough tests on the functions
and post the results on this NG.
I think it would take them months. How would they know they got the
right dates?


That's exactly my question for you. You post code that promises to
accurately calculate all these dates. How do we know you got it
right?


You're talking about July 4, right? Also see:

http://groups.google.com/group/comp....269efa3?hl=en&

Although I am not 100% sure that C. F. Gauss' calculations were
absolutely correct I spent enough time trying to understand the
calculations that I am reasonably sure they are correct.
With holiday code the noobs don't even get a chance to make a mistake.


I think that you're talking about end users. I was
talking about admins--the people who have to maintain the system.


Admins have enough knowledge to decide whether or not the code is
useful.
If I add the
table part for exceptions, how is that any different in providing the
same unexpected benefits?


In the first place, if *you* "add the table part", I'd expect you to
get it, well, not quite right. I base my expectation on what you've
posted so far.


Personal attacks aside, it will be right.
In the second place, the people who maintain systems
are interested in making their own lives easier, not harder. "Add the
table part", and these people have to deal with three different ways
of doing essentially one thing (boolean flags for weekends, an
unreadable string of ones and zeroes for holidays, and a "table part".)
That's for those professionals to decide. Yes, the string of ones and
zeroes is unreadable but not when a form with checkboxes puts the
string together.
You seem to think you have some significant insight that more
experienced programmers lack. I'd like to know what it is.
(Experienced programmers don't give much weight to "preference".)


I was just being polite. I really believe what I'm doing is an
improvement.


I was just being polite, too. What evidence do you offer for your
belief, besides "I thought it was silly", and personal preference?


Having to look up the date of Easter 2006 alone was enough to convince
me that maintaining holiday dates in a table when they could be
computed was silly. I don't think Getz lacks the same insight I have.
I think he might have done something similar in time.
No one who posts here is so good that everything they do
is accepted without question.


I agree. But not every criticism carries equal weight. Your
criticism of Getz's approach--code plus a table of holidays--seems to
boil down to "I think it's silly". There are some people on Usenet
who can stop me dead in my tracks by saying that, but you're not one
of them. Your "improvement" replaces his table with a brittle string
of ones and zeroes, two Boolean flags, and possibly another table. It
doesn't make sense.


I hope that someday my coding and verification will be good enough to
have my comments stop you dead in your tracks. Until then I'll have to
put up with your argument.
You can
disagree about whether or not you think what I did is an improvement
but I already like it better than maintaining holiday dates.


I'm sure you do. But you haven't tried to follow it through the
software lifecycle. Think through what happens to the string of
holiday flags when you add a holiday. Better yet, think through what
happens to programs that use your code when you add a holiday.


This is a very good point. Adding a holiday will make the string one
character longer. The form that creates the string will have to put an
extra digit on the string. Then the code will have to be changed to
use the string to check the correct holidays. There's probably a
better way to do this but I'll be too busy rechecking the functions to
work on it at the moment.

--
Mike Sherrill


I owe you thanks for giving attention to this code. I think everyone
who uses it will eventually benefit from your adversarial stance.

James A. Fortune

Nov 13 '05 #18

P: n/a
ji********@compumarc.com wrote:
I owe you thanks for giving attention to this code. I think everyone
who uses it will eventually benefit from your adversarial stance.

James A. Fortune


Functions to be checked:

IsEaster

Public Function IsEaster(dtTestDate As Date) As Boolean
Dim M As Integer
Dim d As Integer
Dim y As Integer
Dim DT As Date
Dim S1 As Integer
Dim S2 As Integer

IsEaster = False
If Month(dtTestDate) < 3 Or Month(dtTestDate) > 4 Then Exit Function
If Month(dtTestDate) = 3 And Day(dtTestDate) < 22 Then Exit Function
If Month(dtTestDate) = 4 And Day(dtTestDate) > 26 Then Exit Function
y = Year(dtTestDate)
S1 = y \ 100
S2 = (S1 - 17) \ 25
M = (15 + S1 - S1 \ 4 - (S1 - S2) \ 3) Mod 30
d = (19 * (y Mod 19) + M) Mod 30
Select Case d
Case 29:
Select Case M
Case 0, 3, 6, 8, 11, 14, 17, 19, 22, 25, 27:
d = 28
Case 2, 5, 10, 13, 16, 21, 24, 29:
d = 27
End Select
End Select
DT = DateAdd("d", d, DateSerial(y, 3, 22))
DT = DT + (8 - WeekDay(DT)) Mod 7
If Month(dtTestDate) = Month(DT) And Day(dtTestDate) = Day(DT) Then
IsEaster = True
End Function

Method:

Run IsEaster for all years that have Easter dates listed on page 876 in
Oxford Companion to the Year by Bonnie Blackburn and Leofranc
Holford-Strevens. A date in the far future for Easter will be included
as well. Note that the earliest possible date for Easter using C. F.
Gauss' formula is March 22 and the latest possible date is 29 + 6 - 9 =
April 26, so no possible test dates were excluded. For each year the
possible range of test dates can only return one date as True from the
IsEaster function.

1997 30 March
1998 12 April
1999 4 April
2000 23 April
2001 15 April
2002 31 March
2003 20 April
2004 11 April
2005 27 March
2006 16 April
2007 8 April
2008 23 March
2009 12 April
2010 4 April
2011 24 April
2012 8 April
2013 31 March
2014 20 April
2015 5 April
2016 27 March
2017 16 April
2018 1 April
2019 21 April
2020 12 April
....
7485 19 April

Test Driver Code:

Private Sub cmdBeginTest_Click()
Dim lngI As Long
Dim lngJ As Long
Dim dtTest As Date

For lngI = 1997 To 2020
dtTest = DateSerial(lngI, 3, 22)
For lngJ = 0 To 35 '31 - 22 + 1 = 10 March days + 26 April days - 1
for 3/22
If IsEaster(dtTest) Then MsgBox (dtTest)
dtTest = DateAdd("d", 1, dtTest)
Next lngJ
Next lngI
dtTest = DateSerial(7485, 4, 19)
MsgBox (IsEaster(dtTest))
End Sub

Code returned all the dates listed and returned True for 4/19/7485.

Functions to be verified:

NthXDay
LastXDay

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

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, 1), 2) + DayConst - 7)
Mod 7)
End Function

Method:

NthXDay: Since incrementing N simply adds seven it is sufficient to
show that NthXDay works when N = 1. I.e., the first XDay of the month.
This number depends only on which day the first of the month falls.
The 1stXDay is 1 + NumberOfDaysToBeAdded where 0 to 6 are allowed
values for NumberOfDaysToBeAdded. NumberOfDaysToBeAdded is
(DayConstRequested - DayConstFor1stDayOfMonth) Mod 7. But the Mod
function sometimes returns negative numbers instead of numbers in the
range {0, 1, ..., 6} so it must be adjusted to (7 - (DayConstRequested
- DayConstFor1stDayOfMonth)) Mod 7, or equivalently, (7 +
DayConstFor1stDayOfMonth - DayConstRequested) Mod 7. So 1stXDay = (7 +
DayConstRequested - DayConstFor1stDayOfMonth) Mod 7 + 1.

Sample Calculation:
NthXDay(1, vbTuesday, #10/1/05#)
(7 - WeekDay(DateSerial(Year(#10/1/05#), Month(#10/1/05#), 1)) +
vbTuesday) Mod 7 + 1 + (1 - 1) * 7
= (7 - WeekDay(DateSerial(2005, 10, 1)) + 3) Mod 7 + 1 + 0 * 7
= (7 - 7 + 3) Mod 7 + 1
= 4

The first Tuesday in October 2004 is the 4th.

LastXDay: This function goes to the end of the month and backs up 0 to
6 days. So its value is DateSerial(Year(dtD), Month(dtD) + 1,
-NumberOfDaysToSubtract). -NumberOfDaysToSubtract = -
(DayConstEndOfMonth - DayConstRequested) Mod 7, but similarly, = - (7 +
DayConstEndOfMonth - DayConstRequested) Mod 7 = (DayConstRequested -
DayConstEndOfMonth - 7) Mod 7. So, LastXDay = DateSerial(Year(dtD),
Month(dtD) + 1, (DayConstRequested - DayConstEndOfMonth - 7) Mod 7).
DayConstEndOfMonth = Weekday(DateSerial(Year(dtD), Month(dtD) + 1, 0)).
So LastXDay = DateSerial(Year(dtD), Month(dtD) + 1, (-
Weekday(DateSerial(Year(dtD), Month(dtD) + 1, 0)) + DayConst - 7) Mod
7)

This result is slightly different that what I have. Sample
calculations:

LastXDay(#5/1/06#, vbMonday)

DateSerial(2006, 6, (- Weekday(DateSerial(2006, 6, 0)) + 2 - 7) Mod 7)
= DateSerial(2006, 6, (-Weekday(#5/31/06#) - 5) Mod 7)
= DateSerial(2006, 6, (-4 - 5) Mod 7)
= DateSerial(2006, 6, -2)
= #5/29/06#

LastXDay(#5/1/07#, vbMonday)

DateSerial(2007, 6, (- Weekday(DateSerial(2007, 6, 0)) + 2 - 7) Mod 7)
= DateSerial(2007, 6, (-Weekday(#5/31/07#) - 5) Mod 7)
= DateSerial(2007, 6, (-5 - 5) Mod 7)
= DateSerial(2007, 6, -3)
= #5/28/07#

Both versions of LastXDay returned the correct last Monday dates in May
for 2006 through 2011 (if the Date/Time Properties Calendar in Windows
is reliable) but this latest one is cleaner so I'll update the code to
use it.
5/29/06
5/28/07
5/26/08
5/25/09
5/31/10
5/30/11

Holiday definitions to be checked:
Columbus
Labor
Martin Luther King
Memorial
Presidents
Thanksgiving
Veterans

Method:

http://uscode.house.gov/download/pls/5C61.txt

Sec. 6103

Columbus Day
Second Monday in October

Labor Day
First Monday in September

Birthday of Martin Luther King
Third Monday in January

Memorial Day
Last Monday in May

Washington's Birthday (Presidents Day)
Third Monday in February

Thanksgiving Day
Fourth Thursday in November

Veterans Day
November 11

In short, I found no errors in any of the functions with the possible
exception of the old LastXDay function someday getting a different
value than the new LastXDay function. My next change will be to get
rid of the GetWeekdayNumber function and use the Weekday function
instead (with appropriate changes to the numbers) since I no longer
require any weekday numbers starting on Monday.

BTW, to find out if a month has five of a particular day:

'-----Begin module code
Public Function DoesMonthHaveFiveXDays(dtD As Date, DayConst As
Integer) As Boolean
DoesMonthHaveFiveXDays = (NthXDay(5, DayConst, dtD) <=
DaysInMonth(dtD))
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

Public Function DaysInMonth(dtD As Date) As Integer
DaysInMonth = Day(DateSerial(Year(dtD), Month(dtD) + 1, 0))
End Function
'-----End module code

Sample calls:
DoesMonthHaveFiveXDays(#11/1/05#, vbWednesday) returns True
DoesMonthHaveFiveXDays(#11/1/05#, vbThursday) returns False

James A. Fortune

Nov 13 '05 #19

This discussion thread is closed

Replies have been disabled for this discussion.