Need to apply date filter to report selected from list 1

[wbow]

Hi, I'm using a copy of the "Issues" database template in Access 2003 and in the view reports section, after selecting a report I want to offer the user the ability to filter the report by a StartDate and EndDate. This selection is to be applied to different query based fields, depending on which report is selected.
For example, selecting the Closed Issues report the filter would be applied to the Closed Date field in the underlying query. Selecting the open issues report the filter would be applied to the Opened Date field in the underlying query.
I know this could be accomplished by duplicating the queries and reports with filters in place, but that seems inefficient, but I'm prepared to accept that it may be the only way forward.
Many thanks for any help.

 

[dhookom]

I have often created a new column in the report record source query named "FilterDate". For example you would have a new column in the closed issues like:
FilterDate:[Closed Date]
In another record source it might be:
FilterDate: [Opened Date]
You can then create a where condition to open the report like:

Dim strWhere as String
strWhere = "1=1 "
If Not IsNull(Me.txtStartDate) Then
   strWhere = strWhere & " AND FilterDate >=#" & Me.txtStartDate & "# "
End If
If Not IsNull(Me.txtEndDate) Then
   strWhere = strWhere & " AND FilterDate <=#" & Me.txtEndDate & "# "
End If
DoCmd.OpenReport [usual stuff here] WhereCondition:=strWhere

Duane
Hook'D on Access
MS Access MVP

 

[wbow]

OK. I've added a column into the Closed Issues query called Filter:[Closed Date], but where to I put the VBA code?
Do I create a form with a radio button linked to the code?

Sorry to be a bit slow

 

[dhookom]

You would implement this in whatever you meant by "in the view reports section".

You haven't described the current setup. I assume there is a form with a button that opens the report.

Duane
Hook'D on Access
MS Access MVP

 

[wbow]

I'm using a "WhatDates" form with an OK button linked to the following code:
Private Sub cmdPreview_Click()

'Private Sub cmdPreview_Click()
'On Error GoTo Err_Handler 'Remove the single quote from start of this line once you have it working.
'Purpose: Filter a report to a date range.
'Documentation:

http://allenbrowne.com/casu-08.html

'Note: Filter uses "less than the next day" in case the field has a time component.
Dim strReport As String
Dim strDateField As String
Dim strWhere As String
Dim lngView As Long
Const strcJetDate = "\#mm\/dd\/yyyy\#" 'Do NOT change it to match your local settings.

'DO set the values in the next 3 lines.
strReport = "Closed Issues by Date Range" 'Put your report name in these quotes.
strDateField = "[Completed Date]" 'Put your field name in the square brackets in these quotes.
lngView = acViewPreview 'Use acViewNormal to print instead of preview.

'Build the filter string.
If IsDate(Me.txtStartDate) Then
strWhere = "(" & strDateField & " >= " & Format(Me.txtStartDate, strcJetDate) & ")"
End If
If IsDate(Me.txtEndDate) Then
If strWhere <> vbNullString Then
strWhere = strWhere & " AND "
End If
strWhere = strWhere & "(" & strDateField & " < " & Format(Me.txtEndDate + 1, strcJetDate) & ")"
End If

'Close the report if already open: otherwise it won't filter properly.
If CurrentProject.AllReports(strReport).IsLoaded Then
DoCmd.Close acReport, strReport
End If

'Open the report.
'Debug.Print strWhere 'Remove the single quote from the start of this line for debugging purposes.
DoCmd.OpenReport strReport, lngView, , strWhere

Exit_Handler:
Exit Sub

Err_Handler:
If Err.Number <> 2501 Then
MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation, "Cannot open report"
End If
Resume Exit_Handler
End Sub
I've attahed a copy of the template database "Issues" for clarity.

Many thanks for your interest in helping me with this.

 

[dhookom]

Please note I suggested you create a column named "FilterDate" not "Filter".

I think you can change:

 'DO set the values in the next 3 lines.
    strReport = "Closed Issues by Date Range"      'Put your report name in these quotes.
    strDateField = "[Completed Date]" 'Put your field name in the square brackets in these quotes.
    lngView = acViewPreview     'Use acViewNormal to print instead of preview.

to

 'DO set the values in the next 3 lines.
    strReport = "Closed Issues by Date Range"      'Put your report name in these quotes.
    strDateField = "[FilterDate]" 'Put your field name in the square brackets in these quotes.
    lngView = acViewPreview     'Use acViewNormal to print instead of preview.

Duane
Hook'D on Access
MS Access MVP