Applying weekend and holiday exclusion to query result

[Creosote65]

Hi all,

I was looking around for a subroutine or function that would calculate the difference in days between two given dates, excluding weekends and holidays. I have used the faq181-261 as a starting point. I made an input form with an output message box showing the result in days. Everything worked fine.

I would like to a apply the subroutine to a query result. This query would produce many lines of data. I would like to calculate the difference between two date fields in the query's data source (a table) and apply the date exclusion to each result.

Here is the subroutine with which I'm starting:

Private Sub cmdTurnaround_Click()
    Dim StartDate As Date
    Dim EndDate As Date
    Dim dbs As DAO.Database
    Dim rstHolidays As DAO.Recordset
    Dim OpenQuery As DAO.Recordset
    Dim DeltaDays As Long
    Dim Idx As Long
    Dim MyDate As Date
    Dim NumDays As Long
    Dim strCriteria As String
    Dim NumSgn As String * 1

    Set dbs = CurrentDb
    Set rstHolidays = dbs.OpenRecordset("tblHolidays", dbOpenDynaset)
        
    StartDate = Me.txtStartDate
    EndDate = Me.txtEndDate
    
    NumSgn = Chr(35)

    MyDate = Format(StartDate, "Short Date")

    For Idx = CLng(StartDate) To CLng(EndDate)
        Select Case (Weekday(MyDate))
            Case Is = 1     'Sunday
                'Do Nothing, it is NOT a Workday

            Case Is = 7     'Saturday
                'Do Nothing, it is NOT a Workday

            Case Else       'Normal Workday
                strCriteria = "[HoliDate] = " & NumSgn & Format$(MyDate, "yyyy-mm-dd") & NumSgn 'Thanks to "RoyVidar" 2/18/04
                rstHolidays.FindFirst strCriteria
                If (rstHolidays.NoMatch) Then
                    NumDays = NumDays + 1
                Else
                    'Do Nothing, it is NOT a Workday
                End If

        End Select

        MyDate = DateAdd("d", 1, MyDate)

    Next Idx

    DeltaDays = NumDays - 1
    
    MsgBox "The difference is " & DeltaDays & " days.", vbInformation
    
End Sub

Thanks in advance,
Creosote

 

[MichaelRed]

I 'guess' that it is somewhat of a left handed compliment that you were able to 'start' with the faq, although (w/o) checking closely) I don't see the need to do any modification/change to the original. It (the original) works just fine as the function called from a calculated field, with just the two date fields as hte args.







MichaelRed

 

[Creosote65]

Hi MichaelRed,

It definitely isn't a left handed compliment, as you say; I never use sarcasm when I need something. What I am saying is that the original function will return the calculated value between the days. That is fine if I simply want the value.

What I need is a way to incorporate the subroutine to a query result over mutiple records. I simply don't know how to do that.

Anything will help...

Creosote

 

[MichaelRed]

" ... It (the original) works just fine as the function called from a calculated field, with just the two date fields as the args. ... "

SEe the ubiquitous {F1} )aka H E L P). Keyword / Term hiighlighted.





MichaelRed

 

[Creosote65]

Thanks,

It works fine! I have verified my results and they are excatly what I expected. Very cool!

Thanks,
Creosote