Problem with report passing filter

[John]

When opening the form and selecting my multiple items from the listbox
and pressing the button to run the report it displays a box to enter
the criteria instead of using the ones selected on the form. Example
if I select CABH and NY I get to boxes, one that says CABH with a box
to enter criteria and then one for NY which has a box to enter
criteria. If I type CABH in the first one and NY in the second one
the report runs fine.



Query:

SELECT DISTINCTROW [HPRA_Reimb Mgmt Activity Log].Environment,
Count([HPRA_Reimb Mgmt Activity Log].Environment) AS CountEnvir,
[HPRA_Reimb Mgmt Activity Log].[Task Type], [HPRA_Reimb Mgmt Activity
Log].[Current Task Status]
FROM [HPRA_Reimb Mgmt Activity Log]
GROUP BY [HPRA_Reimb Mgmt Activity Log].Environment, [HPRA_Reimb Mgmt
Activity Log].[Task Type], [HPRA_Reimb Mgmt Activity Log].[Current
Task Status]
HAVING ((([HPRA_Reimb Mgmt Activity Log].[Current Task Status])<>"99 -
Done" And ([HPRA_Reimb Mgmt Activity Log].[Current Task Status])<>"99
- InAct"))
ORDER BY [HPRA_Reimb Mgmt Activity Log].Environment, [HPRA_Reimb Mgmt
Activity Log].[Task Type];

-----------------BUTTON ON FORM WITH
LISTBOX----------------------------------------
Private Sub Command14_Click()
On Error GoTo Error_Handler

DoCmd.OpenReport "Count of Tasks By Environment - Select
Environment", acPreview, , GetCriteria()
DoCmd.Close acForm, "frmEnvironmentForReportMultiple"

Exit_Procedure:
Exit Sub

Error_Handler::
MsgBox "An error has occured in this application. " _
& "Please contact your technical support person and " _
& "tell them this information: " _
& vbCrLf & vbCrLf & "Error Number " & Err.Number & ", " _
& Err.Description, _
Buttons:=vbCritical, Title:="RMAL Issue Log Database"

Resume Exit_Procedure
Resume

End Sub

------SETS CRITERIA FOR REPORT FILTER--------------------------
Private Function GetCriteria() As String
Dim stDocCriteria As String
Dim VarItm As Variant


For Each VarItm In Environment.ItemsSelected
stDocCriteria = stDocCriteria & "[Environment] = " &
Environment.Column(0, VarItm) & " OR "
Next

If stDocCriteria <> "" Then
stDocCriteria = Left(stDocCriteria, Len(stDocCriteria) - 4)
Else
stDocCriteria = "True"
End If

GetCriteria = stDocCriteria

End Function


Any suggestions or help is greatly appreciated.

~John

 

[Douglas J Steele]

Didn't I answer this for you a couple of days ago? Since you're using Text
values, they must be enclosed in quotes.

Replace your existing GetCriteria function with

Private Function GetCriteria() As String
Dim stDocCriteria As String
Dim VarItm As Variant

If Me!Environment.ItemsSelected.Count > 0 Then
For Each VarItm In Me!Environment.ItemsSelected
stDocCriteria = stDocCriteria & "'" & Me!Environment.Column(0, VarItm)
& "', "
Next VarItm
stDocCriteria = "[Environment] IN (" & Left(stDocCriteria,
Len(stDocCriteria) - 2) & ")"
Else
stDocCriteria = "True"
End If

GetCriteria = stDocCriteria

End Function


"John" wrote in message

When opening the form and selecting my multiple items from the listbox
and pressing the button to run the report it displays a box to enter
the criteria instead of using the ones selected on the form. Example
if I select CABH and NY I get to boxes, one that says CABH with a box
to enter criteria and then one for NY which has a box to enter
criteria. If I type CABH in the first one and NY in the second one
the report runs fine.



Query:

SELECT DISTINCTROW [HPRA_Reimb Mgmt Activity Log].Environment,
Count([HPRA_Reimb Mgmt Activity Log].Environment) AS CountEnvir,
[HPRA_Reimb Mgmt Activity Log].[Task Type], [HPRA_Reimb Mgmt Activity
Log].[Current Task Status]
FROM [HPRA_Reimb Mgmt Activity Log]
GROUP BY [HPRA_Reimb Mgmt Activity Log].Environment, [HPRA_Reimb Mgmt
Activity Log].[Task Type], [HPRA_Reimb Mgmt Activity Log].[Current
Task Status]
HAVING ((([HPRA_Reimb Mgmt Activity Log].[Current Task Status])<>"99 -
Done" And ([HPRA_Reimb Mgmt Activity Log].[Current Task Status])<>"99
- InAct"))
ORDER BY [HPRA_Reimb Mgmt Activity Log].Environment, [HPRA_Reimb Mgmt
Activity Log].[Task Type];

-----------------BUTTON ON FORM WITH
LISTBOX----------------------------------------
Private Sub Command14_Click()
On Error GoTo Error_Handler

DoCmd.OpenReport "Count of Tasks By Environment - Select
Environment", acPreview, , GetCriteria()
DoCmd.Close acForm, "frmEnvironmentForReportMultiple"

Exit_Procedure:
Exit Sub

Error_Handler::
MsgBox "An error has occured in this application. " _
& "Please contact your technical support person and " _
& "tell them this information: " _
& vbCrLf & vbCrLf & "Error Number " & Err.Number & ", " _
& Err.Description, _
Buttons:=vbCritical, Title:="RMAL Issue Log Database"

Resume Exit_Procedure
Resume

End Sub

------SETS CRITERIA FOR REPORT FILTER--------------------------
Private Function GetCriteria() As String
Dim stDocCriteria As String
Dim VarItm As Variant


For Each VarItm In Environment.ItemsSelected
stDocCriteria = stDocCriteria & "[Environment] = " &
Environment.Column(0, VarItm) & " OR "
Next

If stDocCriteria <> "" Then
stDocCriteria = Left(stDocCriteria, Len(stDocCriteria) - 4)
Else
stDocCriteria = "True"
End If

GetCriteria = stDocCriteria

End Function


Any suggestions or help is greatly appreciated.

~John