Using MS Access/MS Access 2010

Advertisement


Question
I am trying to build an expression or function in Access 2010 to calculate the number of work days between two (2) dates.  I found the below function that you had posted a few years ago, but not sure exactly what I need to do as I am a fairly new Access user.  Here is the function you had posted:
This is the function I use to calculate net workdays between two dates:

Public Function NetWorkdays(dteStart As Date, dteEnd As Date) As Integer

Dim intGrossDays As Integer
Dim dteCurrDate As Date
Dim i As Integer

intGrossDays = DateDiff("d", dteStart, dteEnd)
NetWorkdays = 0

For i = 0 To intGrossDays
dteCurrDate = dteStart + i
If WeekDay(dteCurrDate, vbMonday) < 6 Then
   If IsNull(DLookup("[HolDate]", "Holidays", "[HolDate] = #" & dteCurrDate & "#")) Then
       NetWorkdays = NetWorkdays + 1
   End If
End If
Next i

End Function

This requires a table that lists holidays.

Can you provide me the specifics on how I build this in Access 2010, specifically the start of the process and the steps.  Also, I understand I need to build a table for the "Holidays", which I presume I name the table "Holidays" and the column should be named "Holdate".  If that is not correct, can you tell me exactly what I need to do.  
As a side note, the two (2) dates I have in my database is Date Packet Received and Date Packet Completed.  I want to know how many workdays / time it has taken to complete the packet.

I thank you in advance for any help provided.

Todd

Answer
Correct, you need to build a table name it Holidays, with 2 fields; Holdate (as the Primary Key) and Holiday as the text description of the holiday. Next step is to copy the function into a global module If you don't have a module already created, then open the VBE (Visual Basic Environment) by pressing Ctrl+G. On the icon bar is a use the icon second from the left to create a new module. When it comes time to name it, name it something like modUDFs. Do not name it the same as the function. The next step is to paste the function into that module.

Once that is done, you can use the function as you would any Built-in function. For example, if you want to display the # of days in a control on a form, you would just enter:

=NetWorkDays(startdate, enddate)

as the Controlsource of the control. You would reference the actual controlnames for your startdate and enddate.

Hope this helps,
Scott<>
Blog: http://scottgem.wordpress.com
Website: http://www.diamondassoc.com
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA

Using MS Access

All Answers


Answers by Expert:


Ask Experts

Volunteer


Scottgem

Expertise

I can answer almost all types of questions relating to Microsoft Access usage and application design. My strengths are database and interface design.

Experience

I've been designing databases for over 25 years working with dBase, FoxPro, Approach and Access.

Organizations
Author of Microsoft Office Access 2007 VBA
Techncial Editor for Special Edition Using Microsoft Access 2007 and Access 2007 Forms, Reports & Queries From Que Publishing

Education/Credentials
Brooklyn College BA

©2016 About.com. All rights reserved.