Filtering an Access Report (2000)

Topic

New Reply

Hi all,
In the attached file(PetrolCars.mdb), there is the report

Reply | Quote

Replies

The “elegant” way is to create a form on which the user can specify the date range, and open the report from that form. Let’s say that you have text boxes txtStartDate and txtEndDate. Code to open the report for dates between txtStartDate and txtEndDate could look like this:

Private Function DoReport(fPreview As Boolean)
Const conActionCanceled = 2501
Const conReportName = “rptPetrol/DriveCars”
Dim strFilter As String
On Error GoTo Handle_Err

‘ Check whether dates are valid.
If IsNull(Me.txtStartDate) Then
MsgBox “Enter a Start Date.”, vbExclamation
Me.txtStartDate.SetFocus
Exit Function
ElseIf IsNull(Me.txtEndDate) Then
MsgBox “Enter an End Date.”, vbExclamation
Me.txtEndDate.SetFocus
Exit Function
ElseIf Me.txtEndDate < Me.txtStartDate Then
MsgBox "End Date may not be before Start Date.", vbExclamation
Me.txtEndDate.SetFocus
Exit Function
End If

strFilter = "[Date/Time] Between #" & Format(Me.txtStartDate, "mm/dd/yyyy") & _
"# And #" & Format(Me.txtEndDate, "mm/dd/yyyy") & "#"

If fPreview Then
DoCmd.OpenReport conReportName, acViewPreview, , strFilter
Else
DoCmd.OpenReport conReportName, acViewNormal, strFilter
End If

Exit Function

Handle_Err:
If Err conActionCanceled Then
MsgBox Err.Description, vbExclamation
End If
End Function

Call this function with True as argument to see it in preview mode, or False to send it to the printer without viewing it.

I have attached the database with such a form. It also demonstrates how to refer to the selected date range in the report header. (I removed the other forms to keep size down!)

Reply | Quote

Thankyou so much Hans, your explanation and db was clear-cut and has helped me get a headstart on this access report phenomenon.
Greatly appreciated

Reply | Quote

Thankyou so much Hans, your explanation and db was clear-cut and has helped me get a headstart on this access report phenomenon.
Greatly appreciated

Reply | Quote

The “elegant” way is to create a form on which the user can specify the date range, and open the report from that form. Let’s say that you have text boxes txtStartDate and txtEndDate. Code to open the report for dates between txtStartDate and txtEndDate could look like this:

Private Function DoReport(fPreview As Boolean)
Const conActionCanceled = 2501
Const conReportName = “rptPetrol/DriveCars”
Dim strFilter As String
On Error GoTo Handle_Err

‘ Check whether dates are valid.
If IsNull(Me.txtStartDate) Then
MsgBox “Enter a Start Date.”, vbExclamation
Me.txtStartDate.SetFocus
Exit Function
ElseIf IsNull(Me.txtEndDate) Then
MsgBox “Enter an End Date.”, vbExclamation
Me.txtEndDate.SetFocus
Exit Function
ElseIf Me.txtEndDate < Me.txtStartDate Then
MsgBox "End Date may not be before Start Date.", vbExclamation
Me.txtEndDate.SetFocus
Exit Function
End If

strFilter = "[Date/Time] Between #" & Format(Me.txtStartDate, "mm/dd/yyyy") & _
"# And #" & Format(Me.txtEndDate, "mm/dd/yyyy") & "#"

If fPreview Then
DoCmd.OpenReport conReportName, acViewPreview, , strFilter
Else
DoCmd.OpenReport conReportName, acViewNormal, strFilter
End If

Exit Function

Handle_Err:
If Err conActionCanceled Then
MsgBox Err.Description, vbExclamation
End If
End Function

Call this function with True as argument to see it in preview mode, or False to send it to the printer without viewing it.

I have attached the database with such a form. It also demonstrates how to refer to the selected date range in the report header. (I removed the other forms to keep size down!)

Reply | Quote