The dates have to come from somewhere, so you will need a table of dates.
You can then use a Totals query to group them by the weekday, and get the
count.
Steps:
1. Create a table with one Date/Time field named TheDate.
Mark as primary key. Save the table as tblDate.
Populate it with all the dates in the range you need to cover.
(Use the function below if you wish.)
2. Create a query using your table and tblDate.
Drag tblDate.TheDate into the design grid.
In the Criteria row under this field, enter this (one line):
Between [Forms].[MainForm].[DateStart]
And [Forms].[MainForm].[DateEnd]
3. Choose Parameters on the Query menu.
Enter 2 rows in the dialog that pops up, like this:
[Forms].[MainForm].[DateStart] Date
[Forms].[MainForm].[DateEnd] Date
4. Depress the Total button on the toolbar.
Access adds a Total row to the grid.
In the Total row under the field above, choose Where.
5. In the next column, in the Field row enter:
DOW: Weekday([tblDate].[TheDate])
Accept GroupBy in the Total row under this field.
Choose Ascending in the Sort row.
6. In the next column, choose TheDate field again.
Choose Count in the Total row under this field.
Run the query. The DOW column contains the day-of-the-week (1 to 7 for Sun
to Sat), and the CountOfTheDate gives you the number you wanted.
Here is the function you can use to avoid typing all the dates into the
table by hand:
Function MakeDates(dtStart As Date, dtEnd As Date) As Long
Dim dt As Date
Dim rs As DAO.Recordset
Set rs = DBEngine(0)(0).OpenRecordset("tblDate")
With rs
For dt = dtStart To dtEnd
.AddNew
!TheDate = dt
.Update
Next
End With
rs.Close
Set rs = Nothing
End Function
