workdays with holidays

[Rumple517]

In my Access database project, I need to determine deadline dates based on working days and excluding holidays. I have set up the table as instructed below. The code that was given is for calculating the number of days between two dates. What I need is the code that will give me a date based on a date and the number of days (can vary and be either positive or negative). Can this be modified to do this? I appreciate your help.


CREATE TABLE Calendar
(caldate DATETIME NOT NULL PRIMARY KEY,
workingday CHAR(1) NOT NULL CHECK (workingday IN ('Y','N')) DEFAULT
'Y')

Populate it with as many years as you'll ever need:

INSERT INTO Calendar (caldate) VALUES ('20000101')

WHILE (SELECT MAX(caldate) FROM Calendar)<'2100 1231'
INSERT INTO Calendar (caldate)
SELECT DATEADD(D,DATED IFF(D,'19991231 ',caldate),
(SELECT MAX(caldate) FROM Calendar))
FROM Calendar

Set the non-working days:

UPDATE Calendar SET workingday = 'N'
WHERE DATENAME(DW,cal date) IN ('Saturday','Su nday')

Record any public holidays in the same way.

Now you can easily compute the number of working days between two
dates:

SELECT COUNT(*)
FROM Calendar
WHERE caldate BETWEEN @first_date AND @second_date
AND workingday = 'Y' ;

[Rabbit]

DateAdd("d", #, SomeDate) will give you the date after adding # of days.