Working with work days

[angi35]

Hi,

I'm working in Access 2000. I have a form with a series of date fields, showing the progress of a project from start to completion. There's a set of fields/controls for projected dates (when the project is projected to enter each stage of the process) and another set for actual dates. For my purposes, I'm only working with the projected dates.

There is a formula, based on the size of the project (how many hours it will take), for how many days each step of the process will require. I'm trying to set up the code so that when the projected completion date is entered, and based on the number of hours that has been entered, all the other projected dates will fill in automatically.

So in other words, if someone enters a project that will take 525 hours with a projected completion date (CompletionPJ) of 6/25/09, the database will automatically fill in the projected start date as [CompletionPJ] - 60 (and will fill in all the other projected dates in the middle according to their respective equations).

I've figured out the easy part of this (code is below), but I'm trying to figure out how to get it to count only work days. For instance, in the formula above, it would be [CompletionPJ] - [60 work days, not counting weekends or holidays].

I have a separate calendar table with CalendarID (autonumber), CalendarDate, CalendarDate2, isWorkDay, and isHoliday. (CalendarDate and CalendarDate2 are identical date fields; they make it possible for me to find the number of working days between two dates for other reports in the database.) isWorkDay and isHoliday are yes/no (all weekend dates are false in isWorkDay; weekday holidays are marked true in isHoliday). So I'm trying to figure out how to tell Access to subtract from [completionPJ] a certain number of days where isWorkDay = True and isHoliday = False.

At this point I don't have the Calendar table included in the query for this form, but I'm guessing I'll need to add it (unless it's possible to make VB code check info in a table that's not part of the data source for the form??).

Here's the code I have so far:

Private Sub txtCompletionPJ_AfterUpdate()
 
If Not IsNull(Me![txtHours]) Then
 
    Select Case txtHours
 
    Case 1 To 499
        Me!txtStartPJ.Value = Me!txtCompletionPJ - 30
 
    Case 500 To 999
        Me!txtStartPJ.Value = Me!txtCompletionPJ - 60
 
    Case 1000 To 1499
        Me!txtStartPJ.Value = Me!txtCompletionPJ - 90
 
    Case 1500 To 1999
        Me!txtStartPJ.Value = Me!txtCompletionPJ - 120
 
    Case Is >= 2000
        Me!txtStartPJ.Value = Me!txtCompletionPJ - 180
 
    End Select
 
End If
 
End Sub

In all there are about 12 steps in the process, so there will actually be a dozen lines for each case.

Anybody have an idea to help me out?

Thanks,
Angi

[Kevin Wilcox]

Hi, there are a few examples of workdays functions around if you google ms access workdays function, you could also look at [HTML]http://www.mvps.org/access/datetime/date0012.htm[/HTML]

Also here's a couple of functions that I've borrowed and/or reworked a few times. From what I remember I had problems with the first one, where I couldn't get it to infallibly return the correct number of days (when compared against manual counts), but I use the second one regularly without probs. I know I adapted this from one I saw elsewhere, until it worked for me in my particular context so you may also need to fiddle with it.

One thing, when I first tried to solve this same issue I spent hours and hours fighting with the original functions trying to get them to work properly - the major problem (I subsequently realised) was that I needed to control for nulls within the query formula as well as or instead of from within the formula, otherwise it just locked up and hung. Just a tip that might save you some pain!

HTH
Kevin

Option Compare Database
Option Explicit
 
Function funWorkDaysDifference(dtStartDay As Date, dtEndDay As Date) As Long
'Function designed by Thom Rose - Permission to use is granted as long as you acknowledge the author
'This function calculates the number of workdays between two dates.
'The number of workdays is inclusive of the beginning and ending dates.
'There must be a table present called tblHolidays which contains the field dtObservedDate in the format date/time
'The table must list the dates of holidays to be observed.
'The user may include other fields in that table, for example, a description of the holiday being observed.
 
Dim lngTotalDays As Long
Dim lngTotalWeeks As Long
Dim dtNominalEndDay As Date
Dim lngTotalHolidays As Long
 
'Check to see if dtStartDay > dtEndDay.  If so, then switch the dates
If dtStartDay > dtEndDay Then
    dtNominalEndDay = dtStartDay
    dtStartDay = dtEndDay
    dtEndDay = dtNominalEndDay
End If
'Here are how many weeks are between the two dates
lngTotalWeeks = DateDiff("w", dtStartDay, dtEndDay)
'Here are the number of weekdays in that total week
lngTotalDays = lngTotalWeeks * 5
'Here is the date that is at the end of that many weeks
dtNominalEndDay = DateAdd("d", (lngTotalWeeks * 7), dtStartDay)
'Now add the number of weekdays between the nominal end day and the actual end day
While dtNominalEndDay <= dtEndDay
    If WeekDay(dtNominalEndDay) <> 1 Then
        If WeekDay(dtNominalEndDay) <> 7 Then
            lngTotalDays = lngTotalDays + 1
        End If
    End If
    dtNominalEndDay = dtNominalEndDay + 1
Wend
'Here are how many holiday days there are between the two days
lngTotalHolidays = DCount("dtObservedDate", "tblHolidays", "dtObservedDate <= #" & dtEndDay & "#  AND dtObservedDate >= #" & dtStartDay & "# AND Weekday(dtObservedDate) <> 1 AND Weekday(dtObservedDate) <> 7")
 
'Here are how many total days are between the two dates - this is inclusive of the start and end date
funWorkDaysDifference = lngTotalDays - lngTotalHolidays
 
End Function

Public Function fWorkDays(StartDate As Variant, EndDate As Variant) As Variant
'Public Function fWorkDays(StartDate As Date, EndDate As Date) As Long
On Error GoTo Err_fWorkDays
 
Dim intCount As Integer
Dim rst As DAO.Recordset
Dim DB As DAO.Database
 
Set DB = CurrentDb
Set rst = DB.OpenRecordset("SELECT [HolidayDate] FROM tblHolidays", dbOpenSnapshot)
 
'StartDate = StartDate + 1
'To count StartDate as the 1st day comment out the line above
 
intCount = 0
 
Do While StartDate <= EndDate
 
If Not IsNull(StartDate) And Not IsNull(EndDate) Then
    rst.FindFirst "[HolidayDate] = #" & StartDate & "#"
        If Weekday(StartDate) <> vbSunday And Weekday(StartDate) <> vbSaturday Then
            If rst.NoMatch Then
            intCount = intCount + 1
            End If
        End If
End If
 
StartDate = StartDate + 1
 
Loop
 
fWorkDays = intCount
 
Exit_fWorkDays:
Exit Function
 
Err_fWorkDays:
Select Case Err
 
Case Else
MsgBox Err.Description
Resume Exit_fWorkDays
End Select
 
End Function

[angi35]

Thanks for the ideas. I googled and found a function that does work in theory, but now I'm trying to figure out how to connect this function/module to the code in my form. I haven't worked with functions a whole lot, so I'm still figuring this out.

Here's the function (based on http://www.tech-archive.net/Archive/Access/microsoft.public.access.forms/2008-10/msg00851.html):

Public Function AddWorkDays(OriginalDate As Date, DaysToAdd As Long) As Date
Dim lngAdd As Long
Dim lngDayCount As Long
 
On Error GoTo AddWorkDays_Error
 
If DaysToAdd < 0 Then
    lngAdd = -1
Else
    lngAdd = 1
End If
 
AddWorkDays = OriginalDate
 
Do Until lngDayCount = DaysToAdd
AddWorkDays = DateAdd("d", lngAdd, AddWorkDays)
If Weekday(AddWorkDays, vbMonday) < 6 Then
    If IsNull(DLookup("[holidate]", "HolidaysTable", "[holidate] = #" & _
        AddWorkDays & "#")) Then
        lngDayCount = lngDayCount + lngAdd
    End If
End If
Loop
 
AddWorkDays_Exit:
On Error GoTo 0
    Exit Function
 
AddWorkDays_Error:
    MsgBox "Error " & Err.Number & " (" & Err.Description & _
        ") in procedure AddWorkDays of Module modDateFunctions", vbExclamation, conMsgTitle
    GoTo AddWorkDays_Exit
 
End Function

In my form, txtCompletionPJ corresponds to OriginalDate in the function, and for each date (such as txtStartPJ or txt2ndStepPJ) in each Case I have to specify a certain (negative) number to correspond to DaysToAdd in the function. The end result of the function, AddWorkDays, corresponds to the date I'm trying to find (such as txtStartPJ or txt2ndStepPJ).

Here's another example of the code in my form (two cases with two controls each for now). Can you give me any guidance on how I connect the function to the form? I've tried but I haven't figured it out.

Option Compare Database
Option Explicit
 
Private Sub txtCompletionPJ_AfterUpdate()
 
If Not IsNull(Me![txtHours]) Then
    Select Case txtHours
    Case 1 To 499
        Me!txtStartPJ.Value = Me!txtCompletionPJ - 30
        Me!txt2ndStepPJ.Value = Me!txtCompletionPJ - 25
     Case 500 To 999
        Me!txtStartPJ.Value = Me!txtCompletionPJ - 60
        Me!txt2ndStepPJ.Value = Me!txtCompletionPJ - 53
    End Select
End If
 
End Sub

Thanks,
Angi