none
Ken Sheridan - Help RRS feed

  • Discussão Geral

  • Hi, 

    i´m from Portugal and sorry my english.

    I saw you examples from public database and it´s really good stuff and thanks for that!

    i ´m tryng to get a date and this date if it´s holiday or weekend moves to the next day. i ´m tryng to adjust your functions from your examples and never get the right result.

    Example (1/jan is holiday)

    Colum A(date Original) | Column B (date 1st Working day)

    31 Dez | 31 Dez

    1/jan | 4/jan (because is holiday)

    4/Jan | 4/Jan

    .....

    i ´ve try this one. but give always a diferente result!!

    Public Function AddWorkDays(StartDate As Date, NumDays As Long) As Date
        '....................................................................
        ' Name: AddWorkDays
        ' Inputs: StartDate As Date
        ' NumDays As Long
        ' Returns: 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 ErrHandler
    
        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:
        rst.Close
        Set rst = Nothing
        Set dbs = Nothing
        Exit Function
    
    ErrHandler:
        ' Optionally display error message
        ' MsgBox Err.Description, vbExclamation
        Resume ExitHandler
    End Function

    Thanks.

    sexta-feira, 22 de janeiro de 2021 18:42