grouping / sorting on an Expression - workingdays

nipo
NewLounger
Posts: 3
Joined: 06 Apr 2022, 12:36

grouping / sorting on an Expression - workingdays

Post by nipo »

Hello Hanz / All

i came across one of your very useful code snippets.

Add number of working days to a date excluding holidays and weekends.

it works perfectly.

The only thing i cannot seem to get working as a Group/Sort
it treats it like text and not a date.
i.e. 30th March comes after 1st April

I have tried formatting it but to no avail.

is there a workaround ?

many thanks

nick

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: grouping / sorting on an Expression - workingdays

Post by HansV »

Welcome to Eileen's Lounge!

Could you tell us which code you're using?
Best wishes,
Hans

nipo
NewLounger
Posts: 3
Joined: 06 Apr 2022, 12:36

Re: grouping / sorting on an Expression - workingdays

Post by nipo »

This is th ecode

Code: Select all

Public Function AddWorkDays(StartDate As Variant, NumDays As Long) As Variant
    '....................................................................
    ' Name: AddWorkDays
    ' Inputs:

    '   StartDate As Variant/Date
    '   NumDays As Long
    ' Returns:

    '   Variant/Date
    ' It uses a table named tblHolidays with a field named HolidayDate.
    '....................................................................

    Dim rst As DAO.Recordset
    Dim dbs As DAO.Database
    Dim dtmCurr As Date
    Dim lngCount As Long

    AddWorkDays = Null

    If Not IsDate(StartDate) Then
        Exit Function
    End If

    On Error GoTo ExitHandler

    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("tblHolidays", dbOpenSnapshot)

    lngCount = 0
    dtmCurr = StartDate

    Do While lngCount < NumDays
        dtmCurr = dtmCurr - 1
        If Weekday(dtmCurr, vbMonday) < 6 Then
            rst.FindFirst "[HolidayDate] = #" & Format(dtmCurr, "dd/mm/yyyy") & "#"
            If rst.NoMatch Then
                lngCount = lngCount + 1
            End If
        End If
    Loop

    AddWorkDays = dtmCurr

ExitHandler:
    On Error Resume Next
    rst.Close
    Set rst = Nothing
    Set dbs = Nothing
End Function

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: grouping / sorting on an Expression - workingdays

Post by HansV »

Thanks! The problem is that the function takes blank start dates into account and so by necessity returns a Variant, not a Date type value.
If you are certain that the date field will always be populated, you could use this version:

Code: Select all

Public Function AddWorkDays(StartDate As Date, NumDays As Long) As Date
    '....................................................................
    ' Name: AddWorkDays
    ' Inputs: StartDate As Variant/Date
    ' NumDays As Long
    ' Returns: Variant/Date
    ' Note that this function has been modified to account for holidays.
    ' It requires a table named tblHolidays with a field named HolidayDate.
    '....................................................................

    Dim rst As DAO.Recordset
    Dim dbs As DAO.Database
    Dim dtmCurr As Date
    Dim lngCount As Long

    On Error GoTo ExitHandler

    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("tblHolidays", dbOpenSnapshot)

    lngCount = 0
    dtmCurr = StartDate

    Do While lngCount < NumDays
        dtmCurr = dtmCurr + 1
        If Weekday(dtmCurr, vbMonday) < 6 Then
            rst.FindFirst "[HolidayDate] = #" & Format(dtmCurr, "mm/dd/yyyy") & "#"
            If rst.NoMatch Then
                lngCount = lngCount + 1
            End If
        End If
    Loop

    AddWorkDays = dtmCurr

ExitHandler:
    On Error Resume Next
    rst.Close
    Set rst = Nothing
    Set dbs = Nothing
End Function
Best wishes,
Hans

nipo
NewLounger
Posts: 3
Joined: 06 Apr 2022, 12:36

Re: grouping / sorting on an Expression - workingdays

Post by nipo »

**** YOU ABSOLUTE STAR ****

thank you so much - that is perfect