Setting Combo Box to next Friday on Form open

  • Thread starter Thread starter Steve Lilley
  • Start date Start date
S

Steve Lilley

Hello

Does any one know an efficient way of calculating what the date of the next
Fridays is, or todays date if it happens to be a friday? I know if it's left
to me I'll write a huge "if" chain cheking for every day of the wek and
adding the number of days required to get to friday...:-( Can't be the best
way though I know.


TIA

Regards
Steve
 
Steve,

Use a loop including the Weekday(datValue) function. This returns an
integer representing the day of the week 1=Sun, 2=Mon, ... 6 = Fri etc.

Function NextFriday() as Date
Dim i as long
i = 0
Do while Weekday(Date()+i)<>vbFriday ' 6
i = i + 1
Loop
NextFriday = Date()+i
End Function

Rod
 
Steve,
Add this function to a code module
unction NextFriday(Optional ByVal StartDate As Date) As Date
'Function returns the next frday from a given date
'Eventhough StartDate is an optional argument, it always returns a value
' because it is a date. So, we cannot use the If IsMissing(StartDate)
syntax.

' because it alwayshas a value

Dim intDay As Integer


'If no startdate is supplied, then use today's date
If StartDate = 0 Then
StartDate = Date
End If

'get today's day. Easier to workwith than calling the function repeatedly
intDay = DatePart("w", StartDate)

'If today is a friday then do return today's date
If intDay = vbFriday Then
NextFriday = StartDate

'if it is less than friday then add the difference
ElseIf intDay < vbFriday Then
NextFriday = DateAdd("d", vbFriday - intDay, StartDate)

Else 'it must be saturday, so add 7 days, minus the difference
NextFriday = DateAdd("d", 7 + vbFriday - intDay, StartDate)
End If

End Function
 
Back
Top