Create Better MS Access Reports
Report Builder for Microsoft Access helps you create dynamic, professional-looking
reports fast! The easy-to-understand wizard helps you with complex
tasks like calculated fields, adding subreports, customizing styles,
as well as grouping and sorting. Download a free trial today!
When generating Microsoft Access Reports, it may be necessary to apply parameters based upon dates to allow your users to view data relating to specific information only. The following Microsoft Access database example gives the user various options of populating date fields based upon preset values for today, week, month and year and also allows the user to supply their own values for the data that the report should be based upon
To begin with, we will create the Form that includes various controls as shown below:
As you will see, the form includes two text boxes to supply the Date From and the Date To that will supply criteria to the report. You will also notice that it includes four command buttons for - Today, Week, Month and Year and a command button to preview a report.
The text boxes will allow the user to input a Date From and a Date To that will be used in the report. The command buttons will populate the above two text boxes will values dependant on the system date - taking today's date and calculating to required input from this.
We now need a report that will take the information from this form to use as part of it's criteria. In this example we use a report that includes a single date field shown in the sample below:
This report is based upon a query, that will take the criteria supplied by the form, and use it when generating the report. We supply the criteria to the query by doing the following:
This uses the values entered into the text boxes on the form - txtDateFrom and txtDateTo as criteria to base the report upon.
Each of the buttons on the initial form use code in the On Click event to populate the text boxes. Examples of the code are shown below:
Private Sub cmdtoday_Click() 'Sets the Date From and Date To text boxes 'to Today's Date Me!txtdatefrom = Date Me!txtDateTo = Date End Sub Private Sub cmdweek_Click() 'Sets the Date From and Date To text boxes 'to show complete working week (Mon - Fri) Dim today today = Weekday(Date) Me!txtdatefrom = DateAdd("d", (today * -1) + 2, Date) Me!txtDateTo = DateAdd("d", 6 - today, Date) End Sub Private Sub cmdmonth_Click() 'Sets the Date From and Date To text boxes 'to show complete month (from start to end of current month) Me!txtdatefrom = CDate("01/" & Month(Date) & "/" & Year(Date)) Me!txtDateTo = DateAdd("d", -1, DateAdd("m", 1, Me!txtdatefrom)) End Sub Private Sub cmdyear_Click() 'Sets the Date From and Date To text boxes 'to show complete current year Me!txtdatefrom = CDate("01/01/" & Year(Date)) Me!txtDateTo = DateAdd("d", -1, DateAdd("yyyy", 1, Me!txtdatefrom)) End Sub
The Print Preview Button uses the following procedure to generate the report, whilst also checking to ensure that values are entered into the date text boxes:
Private Sub cmdReport_Click() On Error GoTo Err_cmdReport_Click Dim stDocName As String stDocName = "rptDateParameterReport" 'Check values are entered into Date From and Date To text boxes 'if so run report or cancel request If Len(Me.txtdatefrom & vbNullString) = 0 Or _ Len(Me.txtDateTo & vbNullString) = 0 Then MsgBox "Please ensure that a report date range " & _ "is entered into the form", _ vbInformation, "Required Data..." Exit Sub Else DoCmd.OpenReport stDocName, acPreview End If Exit_cmdReport_Click: Exit Sub Err_cmdReport_Click: MsgBox Err.Description Resume Exit_cmdReport_Click End Sub
To view this example, download the Microsoft Access 2000 Database sample from either the Microsoft Access Reports page or the Microsoft Access Downloads page.
NOTE: To ensure that the sample works correctly, you may need to enter new records into the Orders table, to allow you to view the correct data in the report. This will mean adding records for today, this week, this month and this year.