databasedev.co.uk - database solutions and downloads for microsoft access

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!

Microsoft Access Reports

Date Parameters for generating Microsoft Access Reports:

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:

Completed form that will allow the user to specify date ranges for the report.

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:

Sample of the report to be generated.

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:

The query that the report is based upon, using criteria passed from the form to include the date range required.

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.