Results 1 to 5 of 5
  1. #1
    tomnsd is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2013
    Posts
    17

    Workdays Code

    I have this code that calculates workdays based on two dates. Right now it gives me an error if one of the dates is missing. I would like it to give me a null result if one of the dates is missing. Can someone show me what I need to add?
    Thanks.


    Code:
    Public Function Workdays(dteStart As Date, dteEnd As Date) As Long    Dim lngDate As Long
        Dim rst As DAO.Recordset
        Dim dbs As DAO.Database
            
         If IsNull(dteStart) Or IsNull(dteEnd) Then
         GoTo Exitpoint
         End If
         
         Set dbs = CurrentDb
         Set rst = dbs.OpenRecordset("Holidays", dbOpenSnapshot)
         
       ' NetWorkdays = -1
        ' Check for valid dates.
        If IsDate(dteStart) And IsDate(dteEnd) Then
            ' Strip off any fractional days and just use whole days.
            For lngDate = Int(dteStart) To Int(dteEnd)
            
                If Weekday(lngDate, vbMonday) < 6 Then
                       rst.FindFirst "[Holiday] = #" & Format(lngDate, "mm\/dd\/yyyy") & "#"
                       If rst.NoMatch Then
                       Workdays = Workdays + 1
                       End If
                  
                End If
            Next lngDate
        End If
      rst.Close
      Set rst = Nothing
      Set dbs = Nothing
    
    
    Exitpoint:
       
        Exit Function
    
    
    End Function

  2. #2
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2013
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    You may need to rethink your logic since you cannot make a Long Integer a Null.

  3. #3
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2013
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    It could be a simple as making the Return a Variant instead of a Long Integer. A Variant data type can be set to a Null.

  4. #4
    tomnsd is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2013
    Posts
    17
    Is it possible for you to show me what that would look like in the code? This is way too advanced for me.

  5. #5
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2013
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Code:
    Public Function Workdays(Optional dteStart As Variant, Optional dteEnd As Variant) As Variant
       Dim lngDate As Long
       Dim rst As DAO.Recordset
       Dim dbs As DAO.Database   ' Check for valid dates first.
       If IsDate(dteStart) And IsDate(dteEnd) Then
          Set dbs = CurrentDb
          Set rst = dbs.OpenRecordset("Holidays", dbOpenSnapshot)
          ' Strip off any fractional days and just use whole days.
          Workdays = 0      'Initialize the Variant
          For lngDate = Int(dteStart) To Int(dteEnd)
             If Weekday(lngDate, vbMonday) < 6 Then
                rst.FindFirst "[Holiday] = #" & Format(lngDate, "mm\/dd\/yyyy") & "#"
                If rst.NoMatch Then
                   Workdays = Workdays + 1
                End If
             End If
          Next lngDate
       Else
          Workdays = Null
       End If
       On Error Resume Next
       rst.Close
       Set rst = Nothing
       Set dbs = Nothing
    End Function
    Tested and it seems to work!

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

Similar Threads

  1. Report Code is not allowing return to main code
    By rcwiley in forum Programming
    Replies: 2
    Last Post: 06-16-2013, 10:31 AM
  2. Replies: 7
    Last Post: 05-28-2013, 09:11 AM
  3. Replies: 1
    Last Post: 05-04-2013, 12:19 PM
  4. DateAdd function for workdays?
    By 10 Gauge in forum Programming
    Replies: 2
    Last Post: 04-06-2011, 09:20 AM
  5. Calculating turnaround time on workdays
    By mathonix in forum Queries
    Replies: 1
    Last Post: 04-22-2010, 05:34 PM

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