Hello,

I am trying to use the VBA code from the Work Days Tech Tip video to calculate the # of days past due minus the weekends and holiday various entries are.

The query works, without the days late calculation, once I try to call the daysaged function all I get in my query for the DaysLate result is #Error

I have tried various options unsuccessfully.

I believe I need to equate EndDate to Current Date but am having trouble finding the right way to do so.  Can someone assist?

code

Public Function DaysAged(DueDate As Date, EndDate As Date) As Long
'Public Function DaysAged(DueDate As Date, Now() As Date) As Long   'Doesn't work
'Public Function DaysAged(DueDate As Date) As Long   'Doesn't work

'DueDate is in my table and query

'EndDate is not a field in my table or query, I'm trying to calculate the count of
'how many days past due date up to and including today is minus(-) weekends and holidays

    Dim D As Date, H As Long
    'Dim EndDate As Date ' Doesn't help
    
    DaysAged = 0
    D = DueDate
    'EndDate = "#" & Now() & "#"  'Doesn't work
    'EndDate = "#" & Date & "#"   'Doesn't work
    'EndDate = Date               'Doesn't work
    'EndDate = Day(Date)          'Doesn't work
    'EndDate = Day()              'Doesn't work
        
    While D <= EndDate
    ' Monday through Friday
        If Weekday(D) > 1 And Weekday(D) < 7 Then DaysAged = DaysAged + 1
        D = D + 1
    Wend
    
    ' Holidays in table
        H = Nz(DCount("*", "HolidaysT", "BETWEEN #" & DueDate & _
            "# AND #" & EndDate & "#"), 0)
    DaysAged = DaysAged - H
End Function

Regards,
Keith.