Find all Mondays

[Guest]

When I enter a period ending date on my form, I'd like it to display all
Mondays for that month. Any thoughts?

 

[Geoff]

Assuming:
1. The date is entered in a Texbox named "txtDate".
2. You want to display the list of Mondays in a Listbox named
"lstMondays".
3. The ListBox's RowSourceType property has been set to "Value List"
then you could write code for the Textbox's AfterUpdate event as follows:

Private Sub txtDate_AfterUpdate()

Dim datPeriodEnd As Date
Dim intMonth As Integer
Dim intYear As Integer
Dim datWorking As Date

Dim intWeekday As Integer
Dim strDate As String
Dim I As Integer

' Clear the Listbox:
For I = Me.lstMondays.ListCount - 1 To 0 Step -1
Me.lstMondays.RemoveItem I
Next

' Initialise variables:
datPeriodEnd = Me.txtDate
intMonth = Month(datPeriodEnd)
intYear = Year(datPeriodEnd)
datWorking = DateSerial(intYear, intMonth, 1)
intWeekday = Weekday(datWorking)

' Put Mondays in same month upto datPeriodEnd into Listbox:
Do Until datWorking > datPeriodEnd
If intWeekday = 2 Then
strDate = Format(datWorking, "MM/DD/YYYY")
Me.lstMondays.AddItem strDate
End If
datWorking = DateAdd("d", 1, datWorking)
intWeekday = Weekday(datWorking)
Loop

End Sub