Calculating a Date but Adjusting the End Date if on a Weekend

[Guest]

I am trying to calculate the end date (EndDate) in a report based on a case.
The start date (StartDate) is in another field on the report. I know (to the
best of my ability) how to add the number of days to the StartDate using the
"Select Case" function. However, if the EndDate is on a Saturday or Sunday,
I need to move it to the next weekday (add 2 or 1 more days).

I've tried picking pieces from different posts in the forum, but I still get
EndDate on a Saturday or Sunday. I am somewhere around Beginner and Novice
when it comes to code. Here's what I have so far:

Select Case Me.Customer

Case "Cust1"
If (Weekday((Me.EndDate = DateAdd("d", 58, Me.StartDate)), vbSunday)
= vbSaturday) Then
Me. EndDate = DateAdd("d", 60, Me.StartDate)

ElseIf (Weekday((Me. EndDate = DateAdd("d", 58, Me.StartDate)),
vbSunday) = vbSunday) Then
Me.EndDate = DateAdd("d", 59, Me.StartDate)

Else: Me.EndDate = DateAdd("d", 58, Me.StartDate)

End If

Case "Cust2"
If (Weekday((Me.EndDate = DateAdd("d", 57, Me.StartDate)), vbSunday)
= vbSaturday) Then
Me.EndDate = DateAdd("d", 59, Me.StartDate)

ElseIf (Weekday((Me.EndDate = DateAdd("d", 57, Me.StartDate)),
vbSunday) = vbSunday) Then
Me.EndDate = DateAdd("d", 58, Me.StartDate)

Else: Me.EndDate = DateAdd("d", 57, Me.StartDate)

End If

Case Else
If (Weekday((Me.EndDate = DateAdd("d", 65, Me.StartDate)), vbSunday)
= vbSaturday) Then
Me.EndDate = DateAdd("d", 67, Me.StartDate)

ElseIf (Weekday((Me.EndDate = DateAdd("d", 65, Me.StartDate)),
vbSunday) = vbSunday) Then
Me.EndDate = DateAdd("d", 66, Me.StartDate)

Else: Me.EndDate = DateAdd("d", 65, Me.StartDate)

End If

End Select

Thank you in advance.

 

[Douglas J. Steele]

Take a look in the Date/Time section of "The Access Web"
(http://www.mvps.org/access/) for a couple of options, or look at my
September, 2004 "Access Answers" column in Pinnacle Publiation's "Smart
Access". You can download the column (and sample database) for free at
http://www.accessmvp.com/djsteele/SmartAccess.html

 

[Marshall Barton]

I am trying to calculate the end date (EndDate) in a report based on a case.
The start date (StartDate) is in another field on the report. I know (to the
best of my ability) how to add the number of days to the StartDate using the
"Select Case" function. However, if the EndDate is on a Saturday or Sunday,
I need to move it to the next weekday (add 2 or 1 more days).

I've tried picking pieces from different posts in the forum, but I still get
EndDate on a Saturday or Sunday. I am somewhere around Beginner and Novice
when it comes to code. Here's what I have so far:

Select Case Me.Customer

Case "Cust1"
If (Weekday((Me.EndDate = DateAdd("d", 58, Me.StartDate)), vbSunday)
= vbSaturday) Then
Me. EndDate = DateAdd("d", 60, Me.StartDate)

ElseIf (Weekday((Me. EndDate = DateAdd("d", 58, Me.StartDate)),
vbSunday) = vbSunday) Then
Me.EndDate = DateAdd("d", 59, Me.StartDate)

Else: Me.EndDate = DateAdd("d", 58, Me.StartDate)

End If

[snip similar cases]


It's not quite that complicated:

Case "Cust1"
tmpEnd = DateAdd("d", 58, Me.StartDate))
tmpDay = Weekday(tmpEnd , vbMonday)
If tmpDay > 5 Then
Me. EndDate = DateAdd("d", 8 - tmpDay, tmpEnd)
End If
Case "Cust2"
. . .

 

[Guest]

Thanks Marsh. This was a lot simpler than the way I was trying. Easier to
understand, too.

I am trying to calculate the end date (EndDate) in a report based on a case.
The start date (StartDate) is in another field on the report. I know (to the
best of my ability) how to add the number of days to the StartDate using the
"Select Case" function. However, if the EndDate is on a Saturday or Sunday,
I need to move it to the next weekday (add 2 or 1 more days).

I've tried picking pieces from different posts in the forum, but I still get
EndDate on a Saturday or Sunday. I am somewhere around Beginner and Novice
when it comes to code. Here's what I have so far:

Select Case Me.Customer

Case "Cust1"
If (Weekday((Me.EndDate = DateAdd("d", 58, Me.StartDate)), vbSunday)
= vbSaturday) Then
Me. EndDate = DateAdd("d", 60, Me.StartDate)

ElseIf (Weekday((Me. EndDate = DateAdd("d", 58, Me.StartDate)),
vbSunday) = vbSunday) Then
Me.EndDate = DateAdd("d", 59, Me.StartDate)

Else: Me.EndDate = DateAdd("d", 58, Me.StartDate)

End If

[snip similar cases]


It's not quite that complicated:

Case "Cust1"
tmpEnd = DateAdd("d", 58, Me.StartDate))
tmpDay = Weekday(tmpEnd , vbMonday)
If tmpDay > 5 Then
Me. EndDate = DateAdd("d", 8 - tmpDay, tmpEnd)
End If
Case "Cust2"
. . .