Results 1 to 10 of 10
  1. #1
    rdougherty is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Dec 2016
    Location
    Michigan
    Posts
    91

    Calculating Work Days, help with Null Values for dates

    Hello, I know there are already some topics on Working Days functions, with various features built-in. (Thanks BTW)



    But, I am in need of making sure the calc is Null if/when either of the dates is missing.

    When you use DateDiff(), it does this, which is attractive for my use-case.

    However, the following two codes I have found do not allow the value to display Null for the function's returned value for a row of data:

    Option 1: Forces Nulls to 0.
    https://docs.microsoft.com/en-us/off...ween-two-dates
    Code:
    Function Work_Days(BegDate As Variant, endDate As Variant) As Integer 
     Dim WholeWeeks As Variant
     Dim DateCnt As Variant
     Dim EndDays As Integer
      
     On Error GoTo Err_Work_Days
     
     BegDate = DateValue(BegDate)
     endDate = DateValue(endDate)
     WholeWeeks = DateDiff("w", BegDate, endDate)
     DateCnt = DateAdd("ww", WholeWeeks, BegDate)
     EndDays = 0
     ToNull = Null
     
     Do While DateCnt <= endDate
     If Format(DateCnt, "ddd") <> "Sun" And _
     Format(DateCnt, "ddd") <> "Sat" Then
     EndDays = EndDays + 1
     End If
     DateCnt = DateAdd("d", 1, DateCnt)
     Loop
     
     Work_Days = WholeWeeks * 5 + EndDays
    
    
    Exit Function
     
    Err_Work_Days:
     
     ' If either BegDate or EndDate is Null, return a zero
     ' to indicate that no workdays passed between the two dates.
     
     If Err.Number = 94 Then
     Work_Days = 0
     Exit Function
     Else
     'If some other error occurs, provide a message.
     MsgBox "Error " & Err.Number & ": " & Err.Description
     End If
     
    End Function
    Option 2: Does not handle nulls, and displays #error
    https://www.accessforums.net/showthread.php?t=58953
    Code:
    '---------------------------------------------------------------------------------------' Procedure : fWorkingDays
    ' Author    : --adapted from a Bytes article  by A Dezii
    ' Date      : 16/7/2015
    ' REVISED:5/4/2016
    ' Purpose   :**UPDATED TO ALLOW CHOICE OF WEEKEND DAYS
    ' A  Function to count the number of Workdays between 2 dates, that allows user to select weekend days
    'and  respects holidays
    'Count of workings days returned does not include WeekEndDays nor Federal/Stat Holidays.
    '
    'Create a Table named tHoliday with a single DATE/TIME Field named [HolidayDate].
    'Populate this Table with any and all work related Holidays.
    '
    'Inputs: StartDate, and EndDate, Optional WeekendDays
    '
    'DEFAULT  WeekendDays is "1,7" which represent Saturday and Sunday
    '   based on 1 = sunday, 2 = monday, 3 = tuesday......7 = saturday
    '
    'Returns: an Integer representing the number of Workdays/Business Days
    '
    'There are debug.print statements (commented) that you can uncomment and follow the logic.
    '---------------------------------------------------------------------------------------
    '
    Public Function fWorkingDays(dteStartDate As Date, dteEndDate As Date, Optional WeekendDays As String = "1,7") As Integer
              Dim intCount As Integer
              Dim wkdays As String
    10    wkdays = "1234567"    'normal week days
    20    intCount = 0
    30    'Debug.Print "Weekend days " & WeekendDays
    40    On Error GoTo fWorkingDays_Error
    
    
              'process parameter
    50    If Not WeekendDays Like "[1-9,][1-9,][1-9,]" Then
    60     ' Debug.Print "**error in weekenddays   " & WeekendDays
            Err.Raise 2000, , "Bad value in WeekendDays - must be x,x  where x is number 1 thru 7" _
                & " representing the week end days      1 = sunday 2 = monday 3 = tuesday......7 = saturday"
    70    Else
    80      wkdays = Replace(wkdays, Left(WeekendDays, 1), "")
    90      wkdays = Replace(wkdays, Right(WeekendDays, 1), "")
    100     If Right(wkdays, 1) = "," Then wkdays = Mid(wkdays, 1, Len(wkdays) - 1)
    110     'Debug.Print "using weekdays " & wkdays
    120     End If
    130
    
    
    
    
    140   Do While dteStartDate <= dteEndDate
    
    
    150     If InStr(WeekendDays, WeekDay(dteStartDate)) > 0 Then  ' If a WeekEnd day do nothing
    160         'Debug.Print "Testing days " & dteStartDate & "  " & WeekDay(dteStartDate) & "  is a weekendday"
    170     Else
    
    
    180        ' Debug.Print "Testing days " & dteStartDate & "  " & WeekDay(dteStartDate) & "  is a weekday"
                '  is it a Holiday as posted in tblHolidays?
    190         If DCount("*", "tHoliday", "HolidayDate  = #" & dteStartDate & "#") < 1 Then     'NOT Holiday
    200             intCount = intCount + 1   ' so increment if weekday and not a holiday
    210         Else
    220             'Debug.Print "Testing weekdays " & WeekDay(dteStartDate) & "  is a weekendday and a holiday " & dteStartDate
    230         End If
    240     End If
    250     dteStartDate = dteStartDate + 1
    260   Loop
    270   fWorkingDays = intCount
    
    
    280   On Error GoTo 0
    290   Exit Function
    
    
    fWorkingDays_Error:
    
    
    300   MsgBox "Error " & Err.Number & " in line " & Erl & " (" & Err.Description & ") in procedure fWorkingDays of Module AWF_Related"
    End Function
    Last edited by rdougherty; 08-17-2018 at 01:21 PM. Reason: fix formatting

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    The function would need to return a Variant rather than an Integer. Test inside the function:

    Code:
    If Not IsDate(Variable1) OR Not IsDate(Variable2) Then
      FunctionName = Null
      Exit Function
    End If
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    rdougherty is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Dec 2016
    Location
    Michigan
    Posts
    91
    Quote Originally Posted by pbaldy View Post
    The function would need to return a Variant rather than an Integer. Test inside the function:

    Code:
    If Not IsDate(Variable1) OR Not IsDate(Variable2) Then
      FunctionName = Null
      Exit Function
    End If
    Please explain where to paste this into the existing code (1st example above, please)

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Right after the "On Error.." line.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    rdougherty is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Dec 2016
    Location
    Michigan
    Posts
    91
    Thank you:

    However, why doesn't this work?

    IT still returns an error 94: Invalid use of Null

    Code:
    Option Compare DatabaseOption Explicit
    
    
    
    
    Function Work_Days(BegDate As Variant, endDate As Variant) As Integer
     
     Dim WholeWeeks As Variant
     Dim DateCnt As Variant
     Dim EndDays As Integer
      
     On Error GoTo Err_Work_Days
    
    
     BegDate = DateValue(BegDate)
     endDate = DateValue(endDate)
     WholeWeeks = DateDiff("w", BegDate, endDate)
     DateCnt = DateAdd("ww", WholeWeeks, BegDate)
     EndDays = 0
     
     Do While DateCnt <= endDate
     If Format(DateCnt, "ddd") <> "Sun" And _
     Format(DateCnt, "ddd") <> "Sat" Then
     EndDays = EndDays + 1
     End If
     DateCnt = DateAdd("d", 1, DateCnt)
     Loop
     
     Work_Days = WholeWeeks * 5 + EndDays
    
    
    Exit Function
     
    Err_Work_Days:
     
     ' If either BegDate or EndDate is Null, return a zero
     ' to indicate that no workdays passed between the two dates.
     If Not IsDate(BegDate) Or Not IsDate(endDate) Then
     Work_Days = Null
     Exit Function
     End If
     
     'If Err.Number = 94 Then
     'Work_Days = 0
     'Exit Function
     'Else
     'If some other error occurs, provide a message.
     'MsgBox "Error " & Err.Number & ": " & Err.Description
     'End If
     
    End Function

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Uh, you didn't add the suggested code. Functions within will error on a Null.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    rdougherty is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Dec 2016
    Location
    Michigan
    Posts
    91
    uh, I did. See the "On error" section... I first added it there, and then where it instructs the module to "Go to". Neither worked.

    If it is not inserted into the right place, please describe.

    THANKS!

  8. #8
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I said right after the On Error line. Up at the top of the function. you also didn't change the data type the function returns.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  9. #9
    rdougherty is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Dec 2016
    Location
    Michigan
    Posts
    91
    integer to variant did the trick. the placement of the code doesn't actually matter, it seems. I was asking why inserting it into the existing "On error GoTo" which is functionally the same, didn't work. But, alas the variant type is what I needed.

    Thanks for your help, but next time try not to be so condescending and smug. It is about the third or fourth time someone called an "expert" on this forum has treated me this way in recent months and it is a turn-off to people who visit, just saying.

  10. #10
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I'm sorry if the word "uh" sounded that way, it wasn't meant to be. You seem overly sensitive if that single word affected you. It didn't bother me when you used it with me.

    It is not really the same where you have it, though it may appear so. You're catching the problem after the fact instead of preventing it in the first place.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Calculating number of days between dates
    By PATRICKPBME in forum Forms
    Replies: 5
    Last Post: 09-14-2017, 10:44 AM
  2. Replies: 2
    Last Post: 04-10-2015, 03:51 AM
  3. Replies: 3
    Last Post: 02-24-2012, 01:23 PM
  4. Replies: 4
    Last Post: 09-06-2011, 02:20 PM
  5. Replies: 2
    Last Post: 08-01-2011, 09:30 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums