Counting Workdays in Access

[dchaney]

Hello all, if this was answered already I am sorry. I did a search and did not find anything.

Anyway, I have to create a report on a weekly basis for a customer. Currently I do this in Excel, but I pull all of the data from access. So to save time I am trying to set it up to pull the info and use it in access. I have used the assistance from Counting the Number of Working Days in Access 2007 to create what I am looking for, however there are issues. If I do not have an endDate I get the dredded #Error. How do I have the formula show me the days that have been worked, minus weekends and holidays within Access?

I used the VBA code from the above link and in my Query I have Working days: IIf(IsError(Workdays([Gate1Date],[ResolvedDate])),Workdays([Gate1Date],Date()),Workdays([Gate1Date],[ResolvedDate]))

This code works, but still getting the #Error...

Please help

 

[HiTechCoach]

Access does not have a built in Workdays() function Do you have a User Defined Function (UDF) named Workdays()?

Assuming you have a UDF for Workdays() then try this:

Working days: Workdays([Gate1Date],IIF(IsDate([ResolvedDate]), [ResolvedDate], Date()) )

Here is a WorkDays() fuction
http://msdn.microsoft.com/en-us/library/dd327646(v=office.12).aspx

TIP: Use the "Go Advanced" option to get the expanded editor. The use the "#" tool button for the tag for code.

 

[dchaney]

That is the exact link I used to get the info I needed for the VBA.. the code you gave works like a charm, THANK YOU SO MUCH...

 

[HiTechCoach]

You're welcome.

Good luck with your project.