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.
Calendar CodesI  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
Gemini GVBA & Database ProgrammerAsked:
Who is Participating?
 
Gustav BrockCIOCommented:
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 
    BusinessDays
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));

Open in new window

ee1.PNG
1
 
Gemini GVBA  & Database ProgrammerAuthor Commented:
Wow thanks you have saved me again! :)
0
 
Gustav BrockCIOCommented:
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.

All Courses

From novice to tech pro — start learning today.