Business Days Between Two Dates?

"(PeteCresswell)" wrote in
:

Business days between two dates has got to involve a tblHoliday.

Right now I've got such a table - mostly populated with bank
holidays and I compute biz days between two dates by iterating
through the days one-by-one: if it's a Saturday or Sunday it's
not a biz day.... otherwise I do a table lookup - no hit, then
it's a biz day.

But this is *really* slow for processing a record set of, say,
2000 records.

I'm thinking a faster approach would involve re-creating a table
of business days from some begin date to some end date each time
tblHolidays is updated and then doing some SQL magic to bang a
recordset up against that table and somehow bulk-calculate, for
instance, the number of business days between a bond's maturity
date and today's date... or whether or not a bond matures within
60 business days.

Has anybody come up with such an approach?

I calculate the business interval as follows.
weeks = datediff("ww",dtFrom,dtTo,vbmonday)
then add the days in the zeroeth week, subtract the days after dtTo
from the final week, and subtract the dCount() of the number of days
in tblHolidays where holiday between dtFrom and dtTo

this means one dCount per calculation, as opposed to, if I understand
you, a dLookup for each day in the interval.