Calculating Workdays

[PeterS]

Hi


I have searched the internet looking for a solution but being new to Access I did not understand most of what I found. There is alot of information on VBA but I do not relly understand it and am not sure exactly to place it in the query.

I want to calculate the number of working days between two dates.

My SQL statement is as follows:

SELECT tblEmployeeID_Pk.First_Name, tblEmployeeID_Pk.Last_Name, tblLeave_Type.Description, tblLeaveCount.Start_Date, tblLeaveCount.Finish_Date, DateDiff("d",[Start_Date],[Finish_Date])+1 AS Workdays
FROM tblHolidays INNER JOIN (tblLeave_Type INNER JOIN (tblEmployeeID_Pk INNER JOIN tblLeaveCount ON tblEmployeeID_Pk.EmployeeID_Pk = tblLeaveCount.EmployeeID_Fk) ON tblLeave_Type.Leave_TypeID_Pk = tblLeaveCount.Leave_Type_FK) ON tblHolidays.None_Work_DayID_Pk = tblLeave_Type.Leave_TypeID_Pk;

I get the number of days in the month but I need the number of working days. I have a table called tblHolidays wich includes all the public holidays. Please help

[ranman256]

I have a table of tCalDates. It has every date in a calendar.
qsWorkDays query, pulls all dates that are not 1 nor 7

format([CalDate],"w") = day of week
1 = Sun
7 = Sat

my query to pull workdays from

qsWorkDays but not days from tHolidays.

or qsNoWork query pulls all Sat,Sun, and tHolidays.
then ignore everything in qsNoWork dates.

[June7]

As you discovered, this is a common topic. You will need a VBA procedure. Here is one discussion from this forum with complete code.
https://www.accessforums.net/showthread.php?t=60074&highlight=workdays

Search forum on keyword 'workdays' to see more.

This article shows how to call function in query https://www.techonthenet.com/access/...s/workdays.php

[PeterS]

Thanks ranman
I just want to understand;
1. You have an existing table called [tCalDates] that you created, containing all the calendar dates with each day from Mon - Sun numbered 1 through 7?

2. You created a Query called [qsWorkDays] that pulls all the dates excluding 1 (=Sunday) and 7 (=Saturday)

[ranman256]

Select [datefld] ,format([datefld],"w") as DoW from tCalDates

[PeterS]

Ok thanks guys but I am totally lost and do not understand anything you have said. Please can we start again?

My SQL statement for the query is as follows;

SELECT tblEmployee.First_Name, tblEmployee.Last_Name, tblLeave_Type.Description, tblLeaveCount.Start_Date, tblLeaveCount.Finish_Date, DateDiff("d",[Start_Date],[Finish_Date])+1 AS Workdays
FROM tblHolidays INNER JOIN (tblLeave_Type INNER JOIN (tblEmployee INNER JOIN tblLeaveCount ON tblEmployee.EmployeeID_Pk = tblLeaveCount.EmployeeID_Fk) ON tblLeave_Type.Leave_TypeID_Pk = tblLeaveCount.Leave_Type_FK) ON tblHolidays.None_Work_DayID_Pk = tblLeave_Type.Leave_TypeID_Pk
WHERE (((tblLeave_Type.Description)="Annual Leave") AND ((tblLeaveCount.Start_Date) Between [Forms]![frmAnnualLeaveDaysCount]![txtStart_Date] And [Forms]![frmAnnualLeaveDaysCount]![txtFinish_Date]));

I can calculate the number of days between the Start_Date and the Finish_Date quite easily in query design. From there on I am totally lost. Please can you give me a detailed breakdown of what to do? I would really like to learn this stuff but I do not understand any of the things you are answering me with or directing me to go and look at.

[June7]

1. create a general module in VBA editor

2. copy/paste code from the referenced link

3. test and debug code, modify as needed for your situation

4. build query referencing the function as described in other link or reference function in textbox on form or report

If you don't understand any of these steps, I suggest you undertake some tutorials either on-line, with a book, or a formal classroom.