Multiple Report Filter using form

Z

Zoe

I have a report that contains 3 different dates that I want to allow the user
to filter by (either DateIssued, HearingDate, CompianceDate) and in addition
to this date filter - I also need to be able to further filter by an
additional field (Location, Department, Resondant, Status, Category, etc.)

Currently I have a form that opens the report. On this form are:

To filter by one of the 3 dates - I set up a frame with 3 toggles/buttons
(TogIssued, TogHearing, TogCompli). I have txtStartDate and txtEndDate. The
user can enter the dates and then press one of the 3 toggle buttons to filter
the report by date range for that field. This works fine. I used Allen
Browne's code behind each Togg Button:

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 = "rptViolationDates" 'Put your report name in these
quotes.
strDateField = "[DateIssued]" '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


SECONDLY - In addition to this - I have an OptionGrp (Location, Department,
Resondant, Status, Category, etc.) tied in to a Combo Box (cmbFilterBy).
Then by pressing the cmdFilterReport - this filters the report and works fine
only because I defined the date field as DateIssued. I don't know how to make
this variable and insert either DateIssued, HearingDate, CompianceDate
depending on which one the user chooses. Here is the code behind
cmdFilterReport:

Dim iFilterBy As Integer
Dim iFilterByDate As Integer
Dim iReportType As Integer
Dim iFilterValue As Integer
Dim iFilterStartDate As Date
Dim iFilterEndDate As Date
Const conDateFormat = "\#mm\/dd\/yyyy\#"

If IsNull(cmbFilterBy) Then
MsgBox ("Please select a filter criteria.")
Else
iFilterBy = OptGrpFilterBy 'value from the option group
iFilterValue = cmbFilterBy 'value from the combo box
iFilterStartDate = txtStartDate 'value from txtStartDate
iFilterEndDate = txtEndDate 'value from txtEndDate

Select Case iFilterBy
Case 1 'Location
Reports![rptViolationDates].Filter = "LocationId = " &
iFilterValue & " AND [DateIssued] Between " & Format(iFilterStartDate,
conDateFormat) & " AND " & Format(iFilterEndDate, conDateFormat)

Case 2 'Department
Reports![rptViolationDates].Filter = "DepartmentID = " &
iFilterValue & " AND [DateIssued] Between " & Format(iFilterStartDate,
conDateFormat) & " AND " & Format(iFilterEndDate, conDateFormat)

Case 3 'Facility
Reports![rptViolationDates].Filter = "FacilityID = " &
iFilterValue & " AND [DateIssued] Between " & Format(iFilterStartDate,
conDateFormat) & " AND " & Format(iFilterEndDate, conDateFormat)

Case 4 'Status
Reports![rptViolationDates].Filter = "ViolationStatusID = " &
iFilterValue & " AND [DateIssued] Between " & Format(iFilterStartDate,
conDateFormat) & " AND " & Format(iFilterEndDate, conDateFormat)

Case 5 'Violation Resolved
Reports![rptViolationDates].Filter = "ViolationResolvedID = " &
iFilterValue & " AND [DateIssued] Between " & Format(iFilterStartDate,
conDateFormat) & " AND " & Format(iFilterEndDate, conDateFormat)

Case 6 'Violation Category
Reports![rptViolationDates].Filter = "ViolationCategoryID = " &
iFilterValue & " AND [DateIssued] Between " & Format(iFilterStartDate,
conDateFormat) & " AND " & Format(iFilterEndDate, conDateFormat)

End Select

Reports![rptViolationDates].FilterOn = True
End If


I need to combine the two and I am not sure how to go about doing this? I
hope I explained this okay? Thanks so much for any advice on this.
 
S

Steve Sanford

Hi Zoe,

I think this should work.

Create an Option group with 3 options (radio buttons):

option name option value
Date Issued...............1
Hearing Date.............2
Compliance Date........3

Name the option group "OptGrpDateField"

Create a new button. The caption should be something like "View Report". I
named it "OpenReport".

Put this code in the click event of the button:
'---------------------------------
Private Sub OpenReport_Click()
Dim strReport As String
Dim strDateField As String
Dim strWhere As String
Dim strOptField As String
Dim dteStart As Date
Dim dteEnd As Date
Dim lngView As Long

'Do NOT change it to match your local settings.
Const strcJetDate = "\#mm\/dd\/yyyy\#"

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

'check criteria
If Not IsNull(Me.txtStartDate) Then
If Not IsDate(Me.txtStartDate) Then
MsgBox "Invalid Start Date! Please enter a valid date"
Exit Sub
Else
dteStart = Me.txtStartDate
End If
End If

If Not IsNull(Me.txtEndDate) Then
If Not IsDate(Me.txtEndDate) Then
MsgBox "Invalid End Date! Please enter a valid date"
Exit Sub
Else
dteEnd = DateAdd("d", 1, Me.txtEndDate)
End If
End If

If IsNull(Me.cmbFilterBy) Then
MsgBox "Please select a criteria"
Exit Sub
End If

'select the date field
Select Case Me.OptGrpDateField
Case 1 'Date Issued
strDateField = "[DateIssued]"
Case 2 'Hearing Date
strDateField = "[HearingDate]"
Case 3 'Compliance Date
strDateField = "[ComplianceDate]"
End Select

'Build the WHERE string.
If IsDate(Me.txtStartDate) Then
strWhere = strDateField & " >= " & Format(dteStart, strcJetDate)
End If

If IsDate(Me.txtEndDate) Then
If strWhere <> vbNullString Then
strWhere = strWhere & " AND "
End If
strWhere = strWhere & strDateField & " < " & Format(dteEnd, strcJetDate)
End If

Select Case Me.OptGrpFilterBy
Case 1 'Location
strOptField = "[LocationId] = "
Case 2 'Department
strOptField = "[DepartmentID] = "
Case 3 'Facility
strOptField = "[FacilityID] = "
Case 4 'Status
strOptField = "[ViolationStatusID] = "
Case 5 'Violation Resolved
strOptField = "[ViolationResolvedID] = "
Case 6 'Violation Category
strOptField = "[ViolationCategoryID] = "
End Select

If Len(Trim(strWhere)) > 0 Then
strWhere = strWhere & " AND " & strOptField & Me.cmbFilterBy
Else
strWhere = strOptField & Me.cmbFilterBy
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

'Remove the single quote from the start of this line for debugging purposes.
' Debug.Print strWhere

'Open the report.
DoCmd.OpenReport strReport, lngView, , strWhere

End Sub
'---------------------------------

I did a little testing and it seems to create the proper WHERE string....


HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


Zoe said:
I have a report that contains 3 different dates that I want to allow the user
to filter by (either DateIssued, HearingDate, CompianceDate) and in addition
to this date filter - I also need to be able to further filter by an
additional field (Location, Department, Resondant, Status, Category, etc.)

Currently I have a form that opens the report. On this form are:

To filter by one of the 3 dates - I set up a frame with 3 toggles/buttons
(TogIssued, TogHearing, TogCompli). I have txtStartDate and txtEndDate. The
user can enter the dates and then press one of the 3 toggle buttons to filter
the report by date range for that field. This works fine. I used Allen
Browne's code behind each Togg Button:

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 = "rptViolationDates" 'Put your report name in these
quotes.
strDateField = "[DateIssued]" '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


SECONDLY - In addition to this - I have an OptionGrp (Location, Department,
Resondant, Status, Category, etc.) tied in to a Combo Box (cmbFilterBy).
Then by pressing the cmdFilterReport - this filters the report and works fine
only because I defined the date field as DateIssued. I don't know how to make
this variable and insert either DateIssued, HearingDate, CompianceDate
depending on which one the user chooses. Here is the code behind
cmdFilterReport:

Dim iFilterBy As Integer
Dim iFilterByDate As Integer
Dim iReportType As Integer
Dim iFilterValue As Integer
Dim iFilterStartDate As Date
Dim iFilterEndDate As Date
Const conDateFormat = "\#mm\/dd\/yyyy\#"

If IsNull(cmbFilterBy) Then
MsgBox ("Please select a filter criteria.")
Else
iFilterBy = OptGrpFilterBy 'value from the option group
iFilterValue = cmbFilterBy 'value from the combo box
iFilterStartDate = txtStartDate 'value from txtStartDate
iFilterEndDate = txtEndDate 'value from txtEndDate

Select Case iFilterBy
Case 1 'Location
Reports![rptViolationDates].Filter = "LocationId = " &
iFilterValue & " AND [DateIssued] Between " & Format(iFilterStartDate,
conDateFormat) & " AND " & Format(iFilterEndDate, conDateFormat)

Case 2 'Department
Reports![rptViolationDates].Filter = "DepartmentID = " &
iFilterValue & " AND [DateIssued] Between " & Format(iFilterStartDate,
conDateFormat) & " AND " & Format(iFilterEndDate, conDateFormat)

Case 3 'Facility
Reports![rptViolationDates].Filter = "FacilityID = " &
iFilterValue & " AND [DateIssued] Between " & Format(iFilterStartDate,
conDateFormat) & " AND " & Format(iFilterEndDate, conDateFormat)

Case 4 'Status
Reports![rptViolationDates].Filter = "ViolationStatusID = " &
iFilterValue & " AND [DateIssued] Between " & Format(iFilterStartDate,
conDateFormat) & " AND " & Format(iFilterEndDate, conDateFormat)

Case 5 'Violation Resolved
Reports![rptViolationDates].Filter = "ViolationResolvedID = " &
iFilterValue & " AND [DateIssued] Between " & Format(iFilterStartDate,
conDateFormat) & " AND " & Format(iFilterEndDate, conDateFormat)

Case 6 'Violation Category
Reports![rptViolationDates].Filter = "ViolationCategoryID = " &
iFilterValue & " AND [DateIssued] Between " & Format(iFilterStartDate,
conDateFormat) & " AND " & Format(iFilterEndDate, conDateFormat)

End Select

Reports![rptViolationDates].FilterOn = True
End If


I need to combine the two and I am not sure how to go about doing this? I
hope I explained this okay? Thanks so much for any advice on this.
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Multiple report filter using combo box on form 2
Trouble with Code for date filter on report... 9
Set date range and filter results via form/menu for Report Preview 12
Allen Browne / Filter your report by more criteria 6
Filter Report by Form 2
Open Report with the parameter 1
Filter Report by date from Form 2
Help with WHERE filter on opening report from dialog 4

Top