# Need to calculate 5th and 15th business day each month (with exceptions) in MS Access

Greeting Experts!
I’m looking to query/calculate when a report is due.
I have two monthly reports, one due on the 5th business day and the second on the 15th business day of every month for the next 4 years.
One problem is that our office works a 9/80 schedule (9 hours a day with every other Friday off).  So I made a table tblCalendarCodes and a tblDates.
I  assigned every single day for the next four years with a connecting FK to tblCalendarCodes. I figured that would help me count R “regular days” but most sql and vba loops I’ve seen requires a startdate and enddate to calculate the dates. Anybody have any other functions or ideas? My tables are attached.
Workdays.zip
LVL 1
###### Who is Participating?

CIOCommented:
This query will do:

``````SELECT
tblDates.CalendarID,
tblDates.CalendarDate,
tblDates.Description,
tblDates.WorkHours,
tblDates.CodeID,
tblDates.WeekDayID,
(Select Count(*)
From tblDates As T
Where
T.CodeID=2 And
DateDiff("m",T.CalendarDate, tblDates.CalendarDate)=0 And
T.CalendarDate<=tblDates.CalendarDate) AS
FROM
tblDates
WHERE
(((tblDates.CodeID)=2) AND
(((Select Count(*) From tblDates As T
Where
T.CodeID=2 And
DateDiff("m",T.CalendarDate, tblDates.CalendarDate)=0 And
T.CalendarDate<=tblDates.CalendarDate))=5
Or
((Select Count(*) From tblDates As T
Where
T.CodeID=2 And
DateDiff("m",T.CalendarDate, tblDates.CalendarDate)=0 And
T.CalendarDate<=tblDates.CalendarDate))=15));
``````
1

VBA  & Database ProgrammerAuthor Commented:
Wow thanks you have saved me again! :)
0

CIOCommented:
You are welcome!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.