Gordon:
Below is an Explanation and a Step-by-Step procedure.
I've had to make some guesses and assumptions. These will be evident to you.
You will need to make your own adaptations.
Sorry it's longer than I thought it would be. I wasn't sure how much detail
you would need. And it takes longer to explain something than to do it!
EXPLANATION:
-------------------
The RecordSource of your report needs to be a query that is formed from two
source queries.
The first source query will list data from your table of Week-Commencing
dates. I'll assume that this table contains a date field called
"WeekCommencing" and that there is one record in the table for each date
that begins each week.
The second source query will be a copy of the query you're currently using
as the RecordSource for the report. This second query will have a new
calculated field called "WeekStarting" (to differentiate it from the
"WeekCommencing" field). The WeekStarting field will be calculated by the
query calling a function (the code for which is given below). The function
will calculate the date of the first day in the week of a visit - for
example, if a visit occurs on 26 April 2007 and, if Monday is the first day
of the week, then the "WeekStarting" field will show 23 April 2007. (If your
week starts on a different weekday, then you can easily amend the function
to calculate the date of the weekday you want.)
The query acting as the RecordSource for the report will join the
"WeekCommencing" and "WeekStarting" fields. The join will be edited to
include all records from the "WeekCommencing" table and only those records
that match in the "WeekStarting" table.
The RecordSource query will get its start-date and end-date criteria for the
WeekCommencing field from the criteria form, which will be displayed to the
user by the Report's Open event.
STEP-BY-STEP PROCEDURE
----------------------------------
SOURCE QUERY 1:
1. Create source query 1 so that it lists all records in the
Week-Commencing table in date order.
SOURCE QUERY 2:
2. Create source query 2. This will probably be a copy of your current
query that is acting as the RecordSource for the report. Open the query in
design view and ensure it includes all fields required for the report.
3. Include a field that will always contain data (eg the primary key
field). This field will be used to count the number of visits in each week.
4. To create the calculated "WeekStarting" field, enter the following
expression in the Field row of the next blank column in the design grid:
WeekStarting: StartOfWeek([VisitDate])
Change [VisitDate] to the name of your Visit-Date field.
5. Sort the WeekStarting field in ascending order.
6. Drag your VisitDate field to the Field row of the next blank column to
the right of the WeekStarting column. If the VisitDate field is already in
the grid, uncheck the "Show" box in the new column. Sort the new column in
ascending order.
7. Save and close the query.
STARTOFWEEK FUNCTION
8. Create a new standard module.
9. Paste in the following function:
Public Function StartOfWeek(vntVisit As Variant) As Date
' IN:
'
' vntVisit is either a visit date or Null
' (when there are no visits in a given week).
' RETURNS:
'
' If the incoming vntVisit is a date, this function
' returns the date of the first day in the week of
' the visit; otherwise, returns zero.
' CALLED BY:
'
' Query for Schedule report.
' Let MONDAY be first day of week:
Const FIRSTDAYOFWEEK As Integer = vbMonday
Dim fIsDate As Boolean
Dim intWeekday As Integer
Dim intDiff As Integer
Dim datWeekStart As Date
Dim RetVal As Date
' See if a date has been passed in:
fIsDate = IsDate(vntVisit)
If Not fIsDate Then
RetVal = 0
GoTo Bye
End If
' Get Weekday of visit:
intWeekday = Weekday(vntVisit, FIRSTDAYOFWEEK)
' Get difference between Weekday of visit
' and first Weekday of week:
intDiff = intWeekday - 1
' Subtract difference from visit date:
RetVal = DateAdd("d", -intDiff, vntVisit)
Bye:
StartOfWeek = RetVal
Exit Function
End Function
10. Close the module.
FORM FOR REPORT CRITERIA
11. You said you had created a form on which the user can select the
start date for the report.
12. I created an unbound form as follows:
Form Properties:
----------------
PopUp = Yes
Modal = Yes
Scroll Bars = Neither
Record Selector = No
Navigation Buttons = No
Dividing Lines = No
Auto Resize = Yes
Auto Center = Yes
Border Style = Dialog
Control Box = Yes
Max Min Buttons = None
Close Button = Yes
13. ComboBox in which the user can select a start date:
ComboBox Properties:
-----------------------
Name = "cboStart"
RowSource = Source Query 1 (mentioned above)
AfterUpdate Event = [Event Procedure]
14. TextBox for the end date. The end date is calculated by the
ComboBox's AfterUpdate event.
TextBox Properties:
--------------------
Name = txtEnd
Locked = Yes
15. OK button for the user to click after selecting a two-week period.
OK Button Properties:
----------------------
Name = cmdOK
Enabled = No
Default = Yes
16. Cancel button for the user to abort opening the report.
Cancel Button Properties:
-------------------------
Name = cmdCancel
Cancel = Yes
17. The code behind the criteria form:
Option Compare Database
Option Explicit
Private Sub cboStart_AfterUpdate()
Dim datEnd As Date
' Add 13 days to the start date:
datEnd = DateAdd("d", 13, Me.cboStart)
' Put end date in Textbox:
Me.txtEnd = datEnd
' Enable OK button:
Me.cmdOK.Enabled = True
End Sub
Private Sub cmdCancel_Click()
DoCmd.Close
End Sub
Private Sub cmdOK_Click()
' Hide form to allow report to get
' at data on form:
Me.Visible = False
End Sub
RECORD SOURCE QUERY FOR REPORT
18. Create a new query in Design View.
19. In the Show Table dialog, click the Query tab. Add Source Query 1 and
Source Query 2.
20. Drag the WeekCommencing field to the WeekStarting field to join them.
21. Point to the join line, right-click the line, and select Join
Properties.
22. Click the Option Button for "Include all records from [Source Query
1, the WeekCommencing query] and only those records from [Source Query 2,
the WeekStarting query] where the joined fields are equal".
23. Click OK to close the Join Properties dialog. An arrow should appear
pointing to Source Query 2.
24. Drag the WeekCommencing field (from Source Query 1) to the first
column of the grid. Set its Sort to ascending. (This is for convenience
when testing.)
25. Set the criteria for this field to:
Between [Forms]![frmScheduleCriteria]![cboStart] And
[Forms]![frmScheduleCriteria].[txtEnd]
Amend the above line to reflect the name of your criteria form.
26. Drag the asterisk from Query Source 2 down into the grid to get all
fields.
27. Additionally, drag the VisitDate field from Query Source 2 to the
field row of the last column of the grid. Uncheck its Show property. Set its
Sort row to Ascending.
28. Save the query.
REPORT
29. Create a new report. Select the above RecordSource query as the data
source.
30. In Report Design View, click the Sorting and Grouping button on the
Report-Design toolbar. The Sorting and Grouping dialog opens.
31. In the dialog, click in the first row of the Field/Expression column.
Click the down-arrow and select the WeekCommencing field. In the Group
Properties section in the bottom half of the dialog, click in the Group
Header property and select Yes. Similarly, click in the Group Footer
property and select Yes. Click in the Keep Together property and select
"Whole Group" or "With First Detail".
32. Click in the second row of the Field/Expression column. Click the
down-arrow and select your VisitDate field. You don't need a Group Header
or Footer for this field.
33. The dialog should default to Ascending order for the WeekCommencing
and the VisitDate fields.
34. Close the Sorting and Grouping dialog.
35. Click the Field List button on the Report Design toolbar to open the
Field List.
36. Drag the WeekCommencing field from the Field List to the
WeekCommencing GroupHeader Section of the report. You'll want to select a
suitable font, eg Arial 14 point bold.
37. In the WeekCommencing Group Header, create an unbound Textbox.
TextBox Properties:
--------------------
Name = txtGroupCount
Visible = No
Control Source = =Count([ID])
where [ID] is the name of the field that always contains data, eg the
Primary Key field.
Notice the leading equals sign before Count.
38. In the WeekCommencing Group Header, create a label.
Label Properties:
-----------------
Name = lblNoRecords
Caption = No Records!
Font size/weight as appropriate.
39. Drag the other fields you need to the Detail Section of the report.
40. Drag the bottom of the WeekCommencing Group Footer up or down to
create space between each week of the Schedule.
41. Copy and paste the following code into the Report's class module:
Option Compare Database
Option Explicit
' Name of the criteria form:
Private Const ScheduleCriteriaForm As String = "frmScheduleCriteria"
Private Sub Report_Open(Cancel As Integer)
Dim objFRM As Access.Form
' Open criteria form in dialog mode to suspend
' code execution (and report opening):
DoCmd.OpenForm ScheduleCriteriaForm, acNormal, , , , acDialog
' See if criteria form is still open:
On Error Resume Next
Set objFRM = Forms(ScheduleCriteriaForm)
' Stop report from opening if criteria for is closed:
If Err.Number <> 0 Then
Cancel = True
End If
End Sub
Private Sub Report_Close()
' We're done with criteria form:
DoCmd.Close acForm, ScheduleCriteriaForm, acSaveNo
End Sub
Private Sub Report_NoData(Cancel As Integer)
' Show no data message:
MsgBox "No data for schedule. Report won't open.", _
vbOKOnly + vbInformation, "Information"
' Stop report from opening:
Cancel = True
End Sub
Private Sub GroupHeader0_Format(Cancel As Integer, FormatCount As Integer)
Dim fShow As Boolean
' Evaluate count of records in current group:
fShow = (Me.txtGroupCount = 0)
' Show/hide the "No Records" label:
Me.lblNoRecords.Visible = fShow
' Show message to user:
If fShow Then
MsgBox Me.txtWeekCommencing _
& vbNewLine & vbNewLine _
& "No records for above week!", _
vbOKOnly + vbExclamation, "Information"
End If
End Sub
42. Check the property sheet for the report. The following event
properties should say [Event Procedure]:
On Open
On Close
On No Data
That's it! I hope I've not left anything out. Please post back if you need
further explanation.
Geoff
