I made a little table that contains fiscal year strings ("FY08-09", etc.) with associated begin and end dates, and a form with a combo box to allow the user to select a fiscal year and some buttons to select among the reports, but how, exactly, do I give the dates (for the filter) and the string (for printing) to the query/report?
Here is the query's SQL as it currently stands (it asks for input from the user, but that will change, right?):
Expand|Select|Wrap|Line Numbers
- TRANSFORM Count(DataTable.Number) AS CountOfNumber
- SELECT DataTable.Service, Count(DataTable.Number) AS [Total Of Number]
- FROM DataTable
- WHERE (((DataTable.[Date of Surgery]) Between [FY Begin] And [FY End]))
- GROUP BY DataTable.Service
- PIVOT Format([date of surgery],"mmm") In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");
Expand|Select|Wrap|Line Numbers
- Private Sub VCMC_FY_Case_Count_Click()
- On Error GoTo Err_VCMC_FY_Case_Count_Click
- Dim stDocName As String
- stDocName = "VCMC FY Case Count"
- DoCmd.OpenReport stDocName, acPreview
- Exit_VCMC_FY_Case_Count_Click:
- Exit Sub
- Err_VCMC_FY_Case_Count_Click:
- MsgBox Err.Description
- Resume Exit_VCMC_FY_Case_Count_Click
- End Sub