If Requested_On = 12-Apr-19, then Planned_Completion_Date = 20-Apr-19. But 20-Apr-19 is a Saturday, so add 2 more days.
I created a form with a combo box for the priority (named cboPriority) and used the AfterUpdate event.
Priority |
Requested_On |
Planned_Completion_Date |
High |
12-Apr-19 |
16-Apr-19 |
Medium |
12-Apr-19 |
18-Apr-19 |
Low |
12-Apr-19 |
22-Apr-19 |
Is this correct?
Try this
Code:
Option Compare Database
Option Explicit
Private Sub cboPriority_AfterUpdate()
Dim dRequestDate As Date
Dim dCompleteDate As Date
Dim iNumDays As Integer
Dim tmpDate As Date
Dim DaysToAdd As Integer
Dim i As Integer
dRequestDate = Me.Requested_On
Select Case Me.cboPriority
Case "High"
dCompleteDate = DateAdd("d", 2, Requested_On.Value)
Case "Medium"
dCompleteDate = DateAdd("d", 4, Requested_On.Value)
Case "Low"
dCompleteDate = DateAdd("d", 6, Requested_On.Value)
End Select
iNumDays = DateDiff("d", dRequestDate, dCompleteDate)
DaysToAdd = 0
tmpDate = dRequestDate + 1
'check if any of the dates fall on the weekend
For i = 1 To iNumDays
If Weekday(tmpDate) = 1 Or Weekday(tmpDate) = 7 Then
DaysToAdd = DaysToAdd + 1
End If
tmpDate = DateAdd("d", 1, tmpDate)
Next
dCompleteDate = dCompleteDate + DaysToAdd
'check the Complete Date to see if it is a weekend
If Weekday(dCompleteDate) = 1 Or Weekday(dCompleteDate) = 7 Then
dCompleteDate = dCompleteDate + 2
End If
' put date in control
Me.Planned_Completion_Date = dCompleteDate
End Sub