There are several ways to approach this.
Presumably Completed is a Date/Time type field, so the simplest solution
would be to type this criteria row of the query under the Completed field:
Between ([WeekEnding] - 4) And [WeekEnding]
Then declare the parameter. Choose Parameters on the Query menu, and in the
dialog enter:
WeekEnding Date/Time
When the query runs, it gives the results for the 5-day period up to the
date they specify.
For the query that should return an entire month, declare a Date/Time
parameter named MonthEnding, and use criteria of:
Between DateSerial(Year([MonthEnding]), Month([MonthEnding]), 1) And
[MonthEnding]
If you want to restrict the user to only Fridays, or only the last day of
the month, you will need to use a form to enter the values. You would have a
text box for entering the week ending date, and in its BeforeUpdate event
use the WeekDay() function to test if it really is a Friday. Similarly you
could use the BeforeUpdate event of another text box to check if it is the
last day of the month, e.g.:
If Day(Me.MonthEnding + 1) <> 1 Then
Cancel = True
MsgBox "That's not the last day of the month."
End If
You will then need to change the parameters declared in your query so the
query reads the value from the form. The entry in the Parameters dialog will
look like this:
[Forms].[Form1].[WeekEnding] Date/Time
If you want to get a bit more involved, you could create combo boxes on your
form instead of text boxes, so the user can't choose the wrong date. To feed
them, you might create a table of all the dates, and then set their
RowSource so they only draw the desired dates, e.g.:
SELECT TheDate FROM tblDateWHERE (WeekDay([TheDate]) = 6) ORDER BY
TheDate;
or
SELECT TheDate FROM tblDateWHERE (Day([TheDate] + 1) = 1) ORDER BY
TheDate;
This code will automatically enter the dates into the table for you:
Function MakeDates()
Dim dt As Date
Dim rs As DAO.Recordset
Set rs = DBEngine(0)(0).OpenRecordset("tblDate")
With rs
For dt = #1/1/2004# To #12/31/2019#
.AddNew
!TheDate = dt
.Update
Next
End With
rs.Close
Set rs = Nothing
End Function
If you want to get the dates without the table, there is a somewhat cryptic
way of creating a callback function, and setting it as the RowSourceType of
the combo. You could adapt the example below, which is from the Access 97
help file, and actually returns mondays:
Function ListMondays(fld As Control, id As Variant, row As Variant, col As
Variant, code As Variant) As Variant
'Modified from Access 97 help file.
Dim intOffset As Integer
Select Case code
Case acLBInitialize ' Initialize.
ListMondays = True
Case acLBOpen ' Open.
ListMondays = Timer ' Unique ID.
Case acLBGetRowCount ' Get rows.
ListMondays = 4
Case acLBGetColumnCount ' Get columns.
ListMondays = 1
Case acLBGetColumnWidth ' Get column width.
ListMondays = -1 ' Use default width.
Case acLBGetValue ' Get the data.
intOffset = Abs((9 - Weekday(Date)) Mod 7)
ListMondays = Format(Date + intOffset + 7 * row, "mmmm d")
End Select
End Function
HTH
