S
sabrown
I have a report that contains a number of complex calculations. I want
to be able to filter the report based on the result of these
calculations. However I keep running into problems where the darn
"Enter Parameter Value" dialog pops up when trying to apply the filter.
I assume this is cause I am trying to use a calculated control
"FinalModel" as a criteria in the filter.
Is there any way to get around this? Code is below so you can see the
approach I'm using but the problem is not in the code. I know you may
suggest putting th calculations in the query but the fact is they are
too complex. Once they are in with all the prerequisite field level
calculations the query can't be run.
Private Sub cmdApplyFilter_Click()
Dim strPLANTCD As String
Dim strCURR_MODEL As String 'THIS IS THE TROUBLESOME CALC
Dim strFinalModel As String 'THIS IS THE TROUBLESOME CALC
Dim strFILTER As String
'Check that the report is open
If SysCmd(acSysCmdGetObjectState, acReport, "OPDM LIST for QTRLY
Requote") <> acObjStateOpen Then
MsgBox "You must open the report first."
Exit Sub
End If
'Build criteria string for cboPlantCode field
If IsNull(Me.cboPlantCode.Value) Then
strPLANTCD = "Like '*'"
Else
strPLANTCD = "='" & Me.cboPlantCode.Value & "'"
End If
'Build criteria string for cboCurrModel field
If IsNull(Me.cboCurrModel.Value) Then
strCURR_MODEL = "='" & Me.cboCurrModel.Value & "'"
Else
strCURR_MODEL = "Like '*'"
End If
'Build criteria string for cboSuggModel field
If IsNull(Me.cboSuggModel.Value) Then
strFinalModel = "='" & Me.cboSuggModel.Value & "'"
Else
strFinalModel = "Like '*'"
End If
'Combine strings into a WHERE clause for the filter
strFILTER = "[PLANTCD] " & strPLANTCD & " AND [CURR_MODEL] " &
strCURR_MODEL & " AND [FinalModel] " & strFinalModel
'Apply the filter and switch it on
With Reports![OPDM LIST for QTRLY Requote]
.FILTER = strFILTER
.FilterOn = True
End With
End Sub
Private Sub cmdRemoveFilter_Click()
On Error Resume Next
'Switch the filter off
Reports![OPDM LIST for QTRLY Requote].FilterOn = False
End Sub
Thanks in advance.
Scott
to be able to filter the report based on the result of these
calculations. However I keep running into problems where the darn
"Enter Parameter Value" dialog pops up when trying to apply the filter.
I assume this is cause I am trying to use a calculated control
"FinalModel" as a criteria in the filter.
Is there any way to get around this? Code is below so you can see the
approach I'm using but the problem is not in the code. I know you may
suggest putting th calculations in the query but the fact is they are
too complex. Once they are in with all the prerequisite field level
calculations the query can't be run.
Private Sub cmdApplyFilter_Click()
Dim strPLANTCD As String
Dim strCURR_MODEL As String 'THIS IS THE TROUBLESOME CALC
Dim strFinalModel As String 'THIS IS THE TROUBLESOME CALC
Dim strFILTER As String
'Check that the report is open
If SysCmd(acSysCmdGetObjectState, acReport, "OPDM LIST for QTRLY
Requote") <> acObjStateOpen Then
MsgBox "You must open the report first."
Exit Sub
End If
'Build criteria string for cboPlantCode field
If IsNull(Me.cboPlantCode.Value) Then
strPLANTCD = "Like '*'"
Else
strPLANTCD = "='" & Me.cboPlantCode.Value & "'"
End If
'Build criteria string for cboCurrModel field
If IsNull(Me.cboCurrModel.Value) Then
strCURR_MODEL = "='" & Me.cboCurrModel.Value & "'"
Else
strCURR_MODEL = "Like '*'"
End If
'Build criteria string for cboSuggModel field
If IsNull(Me.cboSuggModel.Value) Then
strFinalModel = "='" & Me.cboSuggModel.Value & "'"
Else
strFinalModel = "Like '*'"
End If
'Combine strings into a WHERE clause for the filter
strFILTER = "[PLANTCD] " & strPLANTCD & " AND [CURR_MODEL] " &
strCURR_MODEL & " AND [FinalModel] " & strFinalModel
'Apply the filter and switch it on
With Reports![OPDM LIST for QTRLY Requote]
.FILTER = strFILTER
.FilterOn = True
End With
End Sub
Private Sub cmdRemoveFilter_Click()
On Error Resume Next
'Switch the filter off
Reports![OPDM LIST for QTRLY Requote].FilterOn = False
End Sub
Thanks in advance.
Scott