use a multi-select listbox to filter a report

I recently tried to use code for "Use a multi-select list box to
filter a report" from Allen Browne in my database. I was able to add
the code and adapt it to my needs, however I am getting an error.

I click Ok and the report opens.

I look in the design view and the filter is there but the IN equals
"" (it is blank, the values are not in there)

The description that appears on the report, which is the strDescrip is
correct. I can see the descriptions of the items I have selected but
the criteria is not picking them up.

I am using Access 2002 - 2003. My criteria is a text field.

Table name is: Tbl_Interns
Criteria field is: Work State Location
Report Name Is: Rpt_WorkingStates

On my report I added a text field called: TxtReportCriteria
And in the control source, I added:=[Report].[OpenArgs]
Here is a sample of the code I am using.
Private Sub CmdPreview_Click()
On Error GoTo Err_Handler
'Purpose: Open the report filtered to the items selected in the
list box.
'Author: Allen J Browne, 2004. http://allenbrowne.com
Dim varItem As Variant 'Selected items
Dim strWhere As String 'String to use as WhereCondition
Dim strDescrip As String 'Description of WhereCondition
Dim lngLen As Long 'Length of string
Dim strDelim As String 'Delimiter for this field type.
Dim strDoc As String 'Name of report to open.

strDelim = """" 'Delimiter appropriate to field type.
See note 1.
strDoc = "Rpt_WorkingStates"

'Loop through the ItemsSelected in the list box.
With Me.LstCategory
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
'Build up the filter from the bound column (hidden).
strWhere = strWhere & strDelim & .ItemData(varItem) &
strDelim & ","
'Build up the description from the text in the visible
column. See note 2.
strDescrip = strDescrip & """" & .Column(1, varItem) &
""", "
End If
Next
End With

'Remove trailing comma. Add field name, IN operator, and brackets.
lngLen = Len(strWhere) - 1
If lngLen 0 Then
strWhere = "Tbl_Interns.[Work State Location]IN (" & Left$
(strWhere, lngLen) & ")"
lngLen = Len(strDescrip) - 2
If lngLen 0 Then
strDescrip = "Selected Work States: " & Left$(strDescrip,
lngLen)
End If
End If

'Report will not filter if open, so close it. For Access 97, see
note 3.
If CurrentProject.AllReports(strDoc).IsLoaded Then
DoCmd.Close acReport, strDoc
End If

'Omit the last argument for Access 2000 and earlier. See note 4.
DoCmd.OpenReport strDoc, acViewPreview, WhereCondition:=strWhere,
OpenArgs:=strDescrip

Exit_Handler:
Exit Sub

Err_Handler:
If Err.Number <2501 Then 'Ignore "Report cancelled" error.
MsgBox "Error " & Err.Number & " - " & Err.Description, ,
"cmdPreview_Click"
End If
Resume Exit_Handler
End Sub

Follow Post Reply

On Sep 10, 11:48 am, Fran <:

I recently tried to use code for "Use a multi-select list box to
filter a report" from Allen Browne in my database. I was able to add
the code and adapt it to my needs, however I am getting an error.

I click Ok and the report opens.

I look in the design view and the filter is there but the IN equals
"" (it is blank, the values are not in there)

The description that appears on the report, which is the strDescrip is
correct. I can see the descriptions of the items I have selected but
the criteria is not picking them up.

I am using Access 2002 - 2003. My criteria is a text field.

Table name is: Tbl_Interns
Criteria field is: Work State Location
Report Name Is: Rpt_WorkingStates

On my report I added a text field called: TxtReportCriteria
And in the control source, I added:=[Report].[OpenArgs]

Here is a sample of the code I am using.

Private Sub CmdPreview_Click()
On Error GoTo Err_Handler
'Purpose: Open the report filtered to the items selected in the
list box.
'Author: Allen J Browne, 2004. http://allenbrowne.com
Dim varItem As Variant 'Selected items
Dim strWhere As String 'String to use as WhereCondition
Dim strDescrip As String 'Description of WhereCondition
Dim lngLen As Long 'Length of string
Dim strDelim As String 'Delimiter for this field type.
Dim strDoc As String 'Name of report to open.

strDelim = """" 'Delimiter appropriate to field type.
See note 1.
strDoc = "Rpt_WorkingStates"

'Loop through the ItemsSelected in the list box.
With Me.LstCategory
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
'Build up the filter from the bound column (hidden).
strWhere = strWhere & strDelim & .ItemData(varItem) &
strDelim & ","
'Build up the description from the text in the visible
column. See note 2.
strDescrip = strDescrip & """" & .Column(1, varItem) &
""", "
End If
Next
End With

'Remove trailing comma. Add field name, IN operator, and brackets.
lngLen = Len(strWhere) - 1
If lngLen 0 Then
strWhere = "Tbl_Interns.[Work State Location]IN (" & Left$
(strWhere, lngLen) & ")"
lngLen = Len(strDescrip) - 2
If lngLen 0 Then
strDescrip = "Selected Work States: " & Left$(strDescrip,
lngLen)
End If
End If

'Report will not filter if open, so close it. For Access 97, see
note 3.
If CurrentProject.AllReports(strDoc).IsLoaded Then
DoCmd.Close acReport, strDoc
End If

'Omit the last argument for Access 2000 and earlier. See note 4.
DoCmd.OpenReport strDoc, acViewPreview, WhereCondition:=strWhere,
OpenArgs:=strDescrip

Exit_Handler:
Exit Sub

Err_Handler:
If Err.Number <2501 Then 'Ignore "Report cancelled" error.
MsgBox "Error " & Err.Number & " - " & Err.Description, ,
"cmdPreview_Click"
End If
Resume Exit_Handler
End Sub

The Error message I receive is "error 2427: You entered an expression
that has no value"

Fran wrote:

I recently tried to use code for "Use a multi-select list box to
filter a report" from Allen Browne in my database. I was able to add
the code and adapt it to my needs, however I am getting an error.

I click Ok and the report opens.

I look in the design view and the filter is there but the IN equals
"" (it is blank, the values are not in there)

The description that appears on the report, which is the strDescrip is
correct. I can see the descriptions of the items I have selected but
the criteria is not picking them up.

I am using Access 2002 - 2003. My criteria is a text field.

Table name is: Tbl_Interns
Criteria field is: Work State Location
Report Name Is: Rpt_WorkingStates

On my report I added a text field called: TxtReportCriteria
And in the control source, I added:=[Report].[OpenArgs]
Here is a sample of the code I am using.
Private Sub CmdPreview_Click()
On Error GoTo Err_Handler
'Purpose: Open the report filtered to the items selected in the
list box.
'Author: Allen J Browne, 2004. http://allenbrowne.com
Dim varItem As Variant 'Selected items
Dim strWhere As String 'String to use as WhereCondition
Dim strDescrip As String 'Description of WhereCondition
Dim lngLen As Long 'Length of string
Dim strDelim As String 'Delimiter for this field type.
Dim strDoc As String 'Name of report to open.

strDelim = """" 'Delimiter appropriate to field type.
See note 1.
strDoc = "Rpt_WorkingStates"

'Loop through the ItemsSelected in the list box.
With Me.LstCategory
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
'Build up the filter from the bound column (hidden).
strWhere = strWhere & strDelim & .ItemData(varItem) &
strDelim & ","
'Build up the description from the text in the visible
column. See note 2.
strDescrip = strDescrip & """" & .Column(1, varItem) &
""", "
End If
Next
End With

'Remove trailing comma. Add field name, IN operator, and brackets.
lngLen = Len(strWhere) - 1
If lngLen 0 Then
strWhere = "Tbl_Interns.[Work State Location]IN (" & Left$
(strWhere, lngLen) & ")"
lngLen = Len(strDescrip) - 2
If lngLen 0 Then
strDescrip = "Selected Work States: " & Left$(strDescrip,
lngLen)
End If
End If

'Report will not filter if open, so close it. For Access 97, see
note 3.
If CurrentProject.AllReports(strDoc).IsLoaded Then
DoCmd.Close acReport, strDoc
End If

'Omit the last argument for Access 2000 and earlier. See note 4.
DoCmd.OpenReport strDoc, acViewPreview, WhereCondition:=strWhere,
OpenArgs:=strDescrip

Exit_Handler:
Exit Sub

Err_Handler:
If Err.Number <2501 Then 'Ignore "Report cancelled" error.
MsgBox "Error " & Err.Number & " - " & Err.Description, ,
"cmdPreview_Click"
End If
Resume Exit_Handler
End Sub

Do you know how to step thru code? Open up the code module and press on
the left side of the code window where you want to start stepping thru
code. I'd do so at the
With Me.LstCategory
line and follow the code and see what's being done. It seems odd that
strWhere has some data but ends up as a "".

On Mon, 10 Sep 2007 09:31:59 -0700, Fran

>On Sep 10, 11:48 am, Fran <:

>I recently tried to use code for "Use a multi-select list box to
filter a report" from Allen Browne in my database. I was able to add
the code and adapt it to my needs, however I am getting an error.

I click Ok and the report opens.

I look in the design view and the filter is there but the IN equals
"" (it is blank, the values are not in there)

The description that appears on the report, which is the strDescrip is
correct. I can see the descriptions of the items I have selected but
the criteria is not picking them up.

I am using Access 2002 - 2003. My criteria is a text field.

Table name is: Tbl_Interns
Criteria field is: Work State Location
Report Name Is: Rpt_WorkingStates

On my report I added a text field called: TxtReportCriteria
And in the control source, I added:=[Report].[OpenArgs]

Here is a sample of the code I am using.

Private Sub CmdPreview_Click()
On Error GoTo Err_Handler
'Purpose: Open the report filtered to the items selected in the
list box.
'Author: Allen J Browne, 2004. http://allenbrowne.com
Dim varItem As Variant 'Selected items
Dim strWhere As String 'String to use as WhereCondition
Dim strDescrip As String 'Description of WhereCondition
Dim lngLen As Long 'Length of string
Dim strDelim As String 'Delimiter for this field type.
Dim strDoc As String 'Name of report to open.

strDelim = """" 'Delimiter appropriate to field type.
See note 1.
strDoc = "Rpt_WorkingStates"

'Loop through the ItemsSelected in the list box.
With Me.LstCategory
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
'Build up the filter from the bound column (hidden).
strWhere = strWhere & strDelim & .ItemData(varItem) &
strDelim & ","
'Build up the description from the text in the visible
column. See note 2.
strDescrip = strDescrip & """" & .Column(1, varItem) &
""", "
End If
Next
End With

'Remove trailing comma. Add field name, IN operator, and brackets.
lngLen = Len(strWhere) - 1
If lngLen 0 Then
strWhere = "Tbl_Interns.[Work State Location]IN (" & Left$
(strWhere, lngLen) & ")"
lngLen = Len(strDescrip) - 2
If lngLen 0 Then
strDescrip = "Selected Work States: " & Left$(strDescrip,
lngLen)
End If
End If

'Report will not filter if open, so close it. For Access 97, see
note 3.
If CurrentProject.AllReports(strDoc).IsLoaded Then
DoCmd.Close acReport, strDoc
End If

'Omit the last argument for Access 2000 and earlier. See note 4.
DoCmd.OpenReport strDoc, acViewPreview, WhereCondition:=strWhere,
OpenArgs:=strDescrip

Exit_Handler:
Exit Sub

Err_Handler:
If Err.Number <2501 Then 'Ignore "Report cancelled" error.
MsgBox "Error " & Err.Number & " - " & Err.Description, ,
"cmdPreview_Click"
End If
Resume Exit_Handler
End Sub

The Error message I receive is "error 2427: You entered an expression
that has no value"

Where does the report text field called: TxtReportCriteria get its data?

"And in the control source, I added:=[Report].[OpenArgs]"
Is 'control source' in Access 2002-2003 the same as 'Record Source' in Access
97? If so, is "Selected Work States: " "VA", "CA", "TX"
a legimate record source?
Also what is the number of your bound?

Add another text box in the report and make its value equal to strDescrip and
let us know what it shows.

Chuck
--

On Mon, 10 Sep 2007 20:17:27 -0400, Chuck

>On Mon, 10 Sep 2007 09:31:59 -0700, Fran

>>On Sep 10, 11:48 am, Fran <:

>>I recently tried to use code for "Use a multi-select list box to
filter a report" from Allen Browne in my database. I was able to add
the code and adapt it to my needs, however I am getting an error.

I click Ok and the report opens.

I look in the design view and the filter is there but the IN equals
"" (it is blank, the values are not in there)

The description that appears on the report, which is the strDescrip is
correct. I can see the descriptions of the items I have selected but
the criteria is not picking them up.

I am using Access 2002 - 2003. My criteria is a text field.

Table name is: Tbl_Interns
Criteria field is: Work State Location
Report Name Is: Rpt_WorkingStates

On my report I added a text field called: TxtReportCriteria
And in the control source, I added:=[Report].[OpenArgs]

Here is a sample of the code I am using.

Private Sub CmdPreview_Click()
On Error GoTo Err_Handler
'Purpose: Open the report filtered to the items selected in the
list box.
'Author: Allen J Browne, 2004. http://allenbrowne.com
Dim varItem As Variant 'Selected items
Dim strWhere As String 'String to use as WhereCondition
Dim strDescrip As String 'Description of WhereCondition
Dim lngLen As Long 'Length of string
Dim strDelim As String 'Delimiter for this field type.
Dim strDoc As String 'Name of report to open.

strDelim = """" 'Delimiter appropriate to field type.
See note 1.
strDoc = "Rpt_WorkingStates"

'Loop through the ItemsSelected in the list box.
With Me.LstCategory
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
'Build up the filter from the bound column (hidden).
strWhere = strWhere & strDelim & .ItemData(varItem) &
strDelim & ","
'Build up the description from the text in the visible
column. See note 2.
strDescrip = strDescrip & """" & .Column(1, varItem) &
""", "
End If
Next
End With

'Remove trailing comma. Add field name, IN operator, and brackets.
lngLen = Len(strWhere) - 1
If lngLen 0 Then
strWhere = "Tbl_Interns.[Work State Location]IN (" & Left$
(strWhere, lngLen) & ")"
lngLen = Len(strDescrip) - 2
If lngLen 0 Then
strDescrip = "Selected Work States: " & Left$(strDescrip,
lngLen)
End If
End If

'Report will not filter if open, so close it. For Access 97, see
note 3.
If CurrentProject.AllReports(strDoc).IsLoaded Then
DoCmd.Close acReport, strDoc
End If

'Omit the last argument for Access 2000 and earlier. See note 4.
DoCmd.OpenReport strDoc, acViewPreview, WhereCondition:=strWhere,
OpenArgs:=strDescrip

Exit_Handler:
Exit Sub

Err_Handler:
If Err.Number <2501 Then 'Ignore "Report cancelled" error.
MsgBox "Error " & Err.Number & " - " & Err.Description, ,
"cmdPreview_Click"
End If
Resume Exit_Handler
End Sub

The Error message I receive is "error 2427: You entered an expression
that has no value"

Where does the report text field called: TxtReportCriteria get its data?

"And in the control source, I added:=[Report].[OpenArgs]"
Is 'control source' in Access 2002-2003 the same as 'Record Source' in Access
97? If so, is "Selected Work States: " "VA", "CA", "TX"
a legimate record source?

legitimate

>Also what is the number of your bound?

bound column

>
Add another text box in the report and make its value equal to strDescrip and
let us know what it shows.

Chuck

forgot to engage brain before operating machine.

Chuck
--