CODEWITHSUNDEEP
vbams-access
Deke
Deke

Reputation: 495

VBA Access using next business (week) day

I'm attempting to write a function that fills in the next business day (excludes weekends) to a [DateCreated] field in a record. I was trying to setup something like this:

Function WD()

'Monday
If [DateCreated] = DateAdd("d", (8 - Weekday(Date, 2))) Then '<---Debug hates this line
[DateCreated] = [DateCreated] = DateAdd("d", (8 - Weekday(Date, 3)) Mod 7, Date)
'Tuesday
ElseIf [DateCreated] = DateAdd("d", (8 - Weekday(Date, 3))) Then
[DateCreated] = [DateCreated] = DateAdd("d", (8 - Weekday(Date, 4)) Mod 7, Date)
'Wednesday
ElseIf [DateCreated] = DateAdd("d", (8 - Weekday(Date, 4))) Then
[DateCreated] = [DateCreated] = DateAdd("d", (8 - Weekday(Date, 5)) Mod 7, Date)
'Thursday
ElseIf [DateCreated] = DateAdd("d", (8 - Weekday(Date, 5))) Then
[DateCreated] = [DateCreated] = DateAdd("d", (8 - Weekday(Date, 6)) Mod 7, Date)
'Friday
ElseIf [DateCreated] = DateAdd("d", (8 - Weekday(Date, 6))) Then
[DateCreated] = [DateCreated] = DateAdd("d", (8 - Weekday(Date, 2)) Mod 7, Date)
End If
End Function

But I get an argument not optional error when attempting to call it.

I'm using the following to code to pull it in:

Public Sub EndofDay_Click()
Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset
Dim nConfirmation As Integer

    nConfirmation = MsgBox("Are you sure you want to complete this Task?", vbInformation + vbYesNo, "Complete Task?")
If nConfirmation = vbYes Then
'Copy Records with Status of 10 In Progress
Set rs1 = CurrentDb.OpenRecordset("tblTasks")
Set rs2 = CurrentDb.OpenRecordset("tblTasks")

rs1.MoveFirst
If Not (rs1.EOF And rs1.BOF) Or Status = 10 Then
Do Until rs1.EOF = True
    If (rs1![Status] = 10) Then
       With rs2
            .AddNew
            ![DateCreated] = WD '<---- Here
            .Update
       End With
    End If
    rs1.MoveNext
Loop
Else
'do nothing
End If

rs1.Close 'Close the recordset
Set rs1 = Nothing 'Clean up
rs2.Close 'Close the recordset
Set rs2 = Nothing 'Clean up

Forms![frmTasks].Form.Requery
Forms![frmTasks].Form.Refresh
End sub

I'm not even sure this is the best way to go about trying this. Any help would be greatly appreciated!

Upvotes: 1

Views: 1505

Answers (3)

Lee Mac
Lee Mac

Reputation: 16025

You were close with your logic, but you omitted the date argument for the DateAdd function, and you were also attempting to redefine the existing in-built Weekday function.

Instead, you could condense the logic to the following:

Function NextWorkingDay(datDate As Date) As Date
    If Weekday(datDate) > 5 Then
        NextWorkingDay = datDate + 9 - Weekday(datDate)
    Else
        NextWorkingDay = datDate + 1
    End If
End Function

Here, if the weekday of the supplied date (counting from Sunday) is greater than 5 (i.e. if the day is Friday or Saturday) the function will add 3 days for Friday and 2 days for Saturday, else 1 day will be added to the date.

This could also be written:

Function NextWorkingDay(datDate As Date) As Date
    Dim i As Integer: i = Weekday(datDate)
    If i < 6 Then i = 8
    NextWorkingDay = datDate + 9 - i
End Function

Upvotes: 0

Parfait
Parfait

Reputation: 107767

Consider a pure SQL solution that can be called with DoCmd.OpenQuery. Doing so, you can avoid any nested multiple, recordset calls. Below updates original DateCreated field by adding needed days if date lands on Fri-Sun (all assuming Monday as start of weekday), otherwise adds a single day.

SQL

UPDATE tblTasks
SET DateCreated = IIF(WeekDay(DateCreated, 1) = 5, DateAdd('d', 3, DateCreated), 
                      IIF(WeekDay(DateCreated, 1) = 6, DateAdd('d', 2, DateCreated), 
                           IIF(WeekDay(DateCreated, 1) = 7, DateAdd('d', 1, DateCreated), 
                               DateAdd('d', 1, DateCreated)
                               ) 
                         )
                  )

VBA

Public Sub EndofDay_Click()
    Dim nConfirmation As Integer

    nConfirmation = MsgBox("Are you sure you want to complete this Task?", _
                           vbInformation + vbYesNo, "Complete Task?")

    If nConfirmation = vbYes Then    
        DoCmd.SetWarnings False
        DoCmd.OpenQuery "myUpdateQuery"
        DoCmd.SetWarnings True
    End If
End Sub

Upvotes: 0

Dick Kusleika
Dick Kusleika

Reputation: 33175

DateAdd takes three arguments and you are only passing it two. The third argument is the date you're starting with.

A function is designed to return a value. Generally you pass in one or more arguments, then you assign the function name to the result and that's what the function returns. As @Josh Eller said, Weekday is already a function name. You can still use it, but it's probably best if you don't.

Here's one way to write a function that will return the next business day. It takes a date as an argument, then adds the proper number of days based on what day of the week the passed in date is.

Function NextBusDay(ByVal dtStart As Date)

    Dim lAdder As Long

    lAdder = 1

    If Weekday(dtStart) = 6 Then lAdder = lAdder + 2
    If Weekday(dtStart) = 7 Then lAdder = lAdder + 1

    NextBusDay = dtStart + lAdder

End Function

You can call it like

![DateCreated] = NextBusDay(Date)

if you want to get the next business date after today. or

![DateCreated] = NextBusDay(![DateCreated])

if you want to change the DateCreated field to the next business date after whatever date is already in there.

Upvotes: 2

Related Questions