It was just a suggestion. And a question re a possible change to your process.
Does the Sup have to enter anything? If so, why? It's just getting the requirements understood.
You can make the startdate calculation into a function (adjust what I have shown).
Do you ever have to get a report for a different week?
Here's a function with some test materials ( may not mean much to you yet)
The function
Code:
'---------------------------------------------------------------------------------------
' Procedure : getSunday
' Author : mellon
' Date : 17-Oct-2017
' Purpose : To determine the most recent Sunday from a given date, or from today if no Date is supplied.
' Then, return the date of the Sunday one week earlier.
'
' If no Date is supplied, today's Date will be used.
' If a Date is supplied, that Date will be used.
' If a non-Date (alpha etc) is supplied, an error message will display and error number 5656 will be returned
'---------------------------------------------------------------------------------------
'
Function getSunday(Optional DateX As Variant) As Date
10 On Error GoTo getSunday_Error
20 If IsMissing(DateX) Then DateX = Date
30 If Not IsDate(DateX) Then
40 MsgBox "Invalid parameter passed---Error!! " & vbCrLf _
& "You must supply a real date or nothing !", vbCritical
50 getSunday = #1/1/1000#
60 err.Raise 5656
70 Exit Function
80 End If
Dim y As Date 'the most recent Sunday
90 y = DateAdd("d", -WeekDay(DateX) + 1, DateX)
100 getSunday = y - 7 'to get the start of the week previous to the most recent Sunday
110
getSunday_Exit:
120 Exit Function
getSunday_Error:
130 MsgBox "Error " & err.number & " in line " & Erl & " (" & err.Description & ") in procedure getSunday of Module ModuleTesting_CanKill"
140 Resume getSunday_Exit
End Function
The test routine ( I manually adjusted the calling routine)
Code:
Sub testgs()
Dim i As Integer
Dim hold As Variant
'test1 = #9/28/2017# getSunday(#9/28/2017#)
'test2 = "xyz" getSunday("xyz"
'test3 = no parameter getSunday()
10 Debug.Print "** getSunday() "
20 hold = getSunday()
30 If hold = #1/1/1000# Then
40 MsgBox "A non date parameter was entered --Error!!!"
50 Exit Sub
60 Else
70 Debug.Print "The most recent Sunday was " & hold + 7 & vbCrLf & "So you could use " & hold & " thru " & hold + 6
80 MsgBox "The most recent Sunday was " & hold + 7 & vbCrLf & "So you could use " & hold & " thru " & hold + 6
90 End If
100
End Sub
Outputs: based on the function call setup
** getSunday(#9/28/2017#) '<<<<<<<<<This let's you choose a different week
The most recent Sunday was 24-Sep-2017
So you could use 17-Sep-2017 thru 23-Sep-2017
** getSunday('xyz')
***Error msgbox displayed
** getSunday() '<<<<<<<<<<<<this is the default
The most recent Sunday was 15-Oct-2017
So you could use 08-Oct-2017 thru 14-Oct-2017