Results 1 to 4 of 4

Thread: call function

  1. #1
    Join Date
    Sep 2014
    Posts
    7

    Unanswered: call function

    I am trying to calculate TAT from Due_Date - Result_Date excluding weekends and holidays.

    So if Due_Date = 10/2/2014 and Result_Date = 10/6/2014, then TAT = 2 (excludes the weekend days 10/4 and 10/5) and only 10/2 and 10/3 are used in the calculation. Currently, I am getting an argument not optional error. I have a table (Holidays) as well, but Im not sure how to best use it. Thanks .

    Function:
    VB
    Code:
     Option Compare Database
    
    Public Function WorkingDays(StartDate As Date, EndDate As Date) As Integer
    '-- Return the number of WorkingDays between StartDate and EndDate
    On Error GoTo err_workingDays
    
    Dim intCount As Integer
    
    If IsDate(StartDate) And IsDate(EndDate) Then
       If EndDate >= StartDate Then
    
          intCount = 0
          Do While StartDate < EndDate
             StartDate = StartDate + 1
             If Weekday(StartDate, vbMonday) <= 5 Then
          '-- Use the following code if you have a "Holiday" table
    '         If Weekday(StartDate, vbMonday) <= 5 And _
                IsNull(DLookup("[Holiday]", "tblHolidays", _
                "[HolDate] = " & Format(StartDate, "\#mm\/dd\/yyyy\#;;;\N\u\l\l"))) Then
    
                intCount = intCount + 1
             End If
          Loop
          WorkingDays = intCount
       Else
          WorkingDays = -1  '-- To show an error
       End If
    Else
       WorkingDays = -1  '-- To show an error
    End If
    
    exit_workingDays:
       Exit Function
    
    err_workingDays:
       MsgBox "Error No:    " & Err.Number & vbCr & _
       "Description: " & Err.Description
       Resume exit_workingDays
    
    End Function
    Call code:
    VB
    Code:
     Private Sub Result_Date_AfterUpdate()
        Me.[TAT] = WorkingDays
    End Sub

  2. #2
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    The WorkingDays function takes two arguments, StartDate and EndDate, but your code

    Me.[TAT] = WorkingDays

    doesn't include the arguments! Try

    Me.TAT = WorkingDays(StartDate, EndDate)

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  3. #3
    Join Date
    Sep 2014
    Posts
    7
    I get an ByRef argument type mismatch error, with the StartDate highlighted.

    Also, how does it know that TAT = Due_Date - Result_Date?

    Thanks.

  4. #4
    Join Date
    Sep 2014
    Posts
    7
    I figured it out, thanks.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •