WorkDays Function

D Willett
SilverLounger
Posts: 1728
Joined: 25 Jan 2010, 08:34
Location: Stoke on Trent - Staffordshire - England

WorkDays Function

Post by D Willett »

Hi. I'm working with the workdays function from Microsoft, I have something misplaced somewhere causing it to fail.
Any chance one of you guys could take a look:

The module:

Code: Select all

Public Function Workdays(ByRef startDate As Date, _
     ByRef endDate As Date, _
     Optional ByRef strHolidays As String = "Holidays" _
     ) As Integer
    ' Returns the number of workdays between startDate
    ' and endDate inclusive.  Workdays excludes weekends and
    ' holidays. Optionally, pass this function the name of a table
    ' or query as the third argument. If you don't the default
    ' is "Holidays".
    On Error GoTo Workdays_Error
    Dim nWeekdays As Integer
    Dim nHolidays As Integer
    Dim strWhere As String
    
    ' DateValue returns the date part only.
    startDate = DateValue(startDate)
    endDate = DateValue(endDate)
    
    nWeekdays = Weekdays(startDate, endDate)
    If nWeekdays = -1 Then
        Workdays = -1
        GoTo Workdays_Exit
    End If
    
    strWhere = "[Holiday] >= #" & startDate _
        & "# AND [Holiday] <= #" & endDate & "#"
    
    ' Count the number of holidays.
    nHolidays = DCount(Expr:="[Holiday]", _
        Domain:=strHolidays, _
        Criteria:=strWhere)
    
    Workdays = nWeekdays - nHolidays
    
Workdays_Exit:
    Exit Function
    
Workdays_Error:
    Workdays = -1
    MsgBox "Error " & Err.Number & ": " & Err.Description, _
        vbCritical, "Workdays"
    Resume Workdays_Exit
    
End Function
The holidays table is a linked table called "tblHolidays"

The two fields I am using as startdate and enddate are:
[InProgressDate]
[ECD]

In the query I have:
Working days: Workdays([InProgressDate],[ECD])

I'm getting function not defined in the VBE.
Many Thanks
Cheers ...

Dave.

User avatar
Rudi
gamma jay
Posts: 25455
Joined: 17 Mar 2010, 17:33
Location: Cape Town

Re: WorkDays Function

Post by Rudi »

Hi Dave,

I think that the source where you collected this function from provided additional functions. This Workdays(...) function you copied from this source is dependent on the additional functions on the page from which you copied. Workdays(...) refers to Weekdays(...), so in addition to Workdays in your module, you also need to copy the Weekdays(...) function into the same module. When you have both functions, the code will run fine.
Regards,
Rudi

If your absence does not affect them, your presence didn't matter.

D Willett
SilverLounger
Posts: 1728
Joined: 25 Jan 2010, 08:34
Location: Stoke on Trent - Staffordshire - England

Re: WorkDays Function

Post by D Willett »

Hi Rudi
Great stuff ( haven't tried it yet).
I've copied the weekday module also into the project, do I need to reference the tblHolidays within any of the modules?
This is the link to the Microsoft site:
http://msdn.microsoft.com/en-us/library ... e.12).aspx
Cheers ...

Dave.

User avatar
Rudi
gamma jay
Posts: 25455
Joined: 17 Mar 2010, 17:33
Location: Cape Town

Re: WorkDays Function

Post by Rudi »

No. You don't need to do any more in the code.
If you want holidays to be subtracted from the result, you must ensure that you have a table called Holidays in your database. In this holidays table, edit the dates to reflect the public holidays and the code will automatically reference this table and subtract the amount of days greater than your start date and less than your end date.
Regards,
Rudi

If your absence does not affect them, your presence didn't matter.

D Willett
SilverLounger
Posts: 1728
Joined: 25 Jan 2010, 08:34
Location: Stoke on Trent - Staffordshire - England

Re: WorkDays Function

Post by D Willett »

Working all fine Rudi.

Thanks for the help. :-)
Cheers ...

Dave.

User avatar
Rudi
gamma jay
Posts: 25455
Joined: 17 Mar 2010, 17:33
Location: Cape Town

Re: WorkDays Function

Post by Rudi »

If you are interested, here is a good web page containing additional workday functions...
http://access.mvps.org/access/datetime/date0012.htm
Last edited by Rudi on 16 Oct 2014, 17:32, edited 1 time in total.
Regards,
Rudi

If your absence does not affect them, your presence didn't matter.

D Willett
SilverLounger
Posts: 1728
Joined: 25 Jan 2010, 08:34
Location: Stoke on Trent - Staffordshire - England

Re: WorkDays Function

Post by D Willett »

Hi Rudi. Great information. Thanks again.
Cheers ...

Dave.