Hi, first time posting here and hope this question hasn't already been answered elsewhere. Got frame and option group help from Martin Green's Access Tips to create an action tracking option group to filter records for a report.
The options for the "Apply Filter" button for filtering on the Action Status are: Open, Closed, Late, Overdue and All.
Everything works fine for the filter except that for viewing Open actions, I would like to see both Open and Overdue actions as no action can be Overdue (for closure) without also being Open.
I have amended the Martin Green code accordingly and here it is below:
Private Sub cmdApplyFilter_Click()
Dim strSource As String
Dim strOwner As String
Dim strContract As String
Dim strDivision As String
Dim strStatus As String
Dim strFilter As String
' Check that the report is open
If SysCmd(acSysCmdGetObjectState, acReport, "rptMainData") <> acObjStateOpen Then
MsgBox "You must open the report first."
Exit Sub
End If
' Build criteria string for Source field
If IsNull(cboSource.Value) Then
strSource = "Like '*'"
Else
strSource = "='" & cboSource.Value & "'"
End If
' Build criteria string for Owner field
If IsNull(cboOwner.Value) Then
strOwner = "Like '*'"
Else
strOwner = "='" & cboOwner.Value & "'"
End If
' Build criteria string for Contract field
If IsNull(cboContract.Value) Then
strContract = "Like '*'"
Else
strContract = "='" & cboContract.Value & "'"
End If
' Build criteria string for Division field
If IsNull(cboDivision.Value) Then
strDivision = "Like '*'"
Else
strDivision = "='" & cboDivision.Value & "'"
End If
' Build criteria string for Status field
Select Case fraStatus.Value
Case 1
strStatus = "='Open'"
Case 2
strStatus = "='Closed'"
Case 3
strStatus = "='Late'"
Case 4
strStatus = "='Overdue'"
Case 5
strStatus = "Like '*'"
End Select
' Combine criteria strings into a WHERE clause for the filter
strFilter = "[Contract] " & strContract & " AND [Source] " & strSource & " AND [Owner] " & strOwner _
& " AND [Division] " & strDivision & " AND [Status] " & strStatus
' Apply the filter and switch it on
With Reports![rptMainData]
.Filter = strFilter
.FilterOn = True
.txtReportSubtitle.Value = _
"Division: " & cboDivision.Value
End With
All assistance much appreciated,
Byron