Filter not working

[kittyfool]

Hi Gurus,

I got the following code to generate a report based on the filter in the
listbox. However, when i hit the command button, the report will come out but
without the details of each person. My report is based on union of 3 query.

Can anybody help me with that?

I dont understand this part.
What do i have to put in the [type]?

If lngLen > 0 Then
strWhere = "[Type] IN (" & Left$(strWhere, lngLen) & ")"
lngLen = Len(strDescrip) - 2
If lngLen > 0 Then
strDescrip = "criteria: " & Left$(strDescrip, lngLen)


Private Sub Command11_Enter()
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 = "DPM_Report"

'Loop through the ItemsSelected in the list box.
With Me.lstteam
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 = "[Type] IN (" & Left$(strWhere, lngLen) & ")"
lngLen = Len(strDescrip) - 2
If lngLen > 0 Then
strDescrip = "criteria: " & 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

 

[Guest]

"Type" is a reserved word in Access and shouldn't be used to name objects; it
also isn't very descriptive - I would use strCustType (if it is text).

I don't understand your question. In this line

strWhere = "[Type] IN (" & Left$(strWhere, lngLen) & ")"

"Type" is the name of the field you want to filter on. From your jpg of the
results of the query, It looks like there are no entries in the field "Type".
If there are entries, you should look at the bound column of the list box. Is
the bound column of the list box the same type as the field "Type" i.e. both
strings or both long integers?


Try this adding a msgbox line:

strWhere = "[Type] IN (" & Left$(strWhere, lngLen) & ")"

MsgBox strWhere ' for debugging

What is the result?

Also, would you post the SQL of the list box "lstteam"


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

Hi Gurus,

I got the following code to generate a report based on the filter in the
listbox. However, when i hit the command button, the report will come out but
without the details of each person. My report is based on union of 3 query.

Can anybody help me with that?

I dont understand this part.
What do i have to put in the [type]?

If lngLen > 0 Then
strWhere = "[Type] IN (" & Left$(strWhere, lngLen) & ")"
lngLen = Len(strDescrip) - 2
If lngLen > 0 Then
strDescrip = "criteria: " & Left$(strDescrip, lngLen)


Private Sub Command11_Enter()
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 = "DPM_Report"

'Loop through the ItemsSelected in the list box.
With Me.lstteam
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 = "[Type] IN (" & Left$(strWhere, lngLen) & ")"
lngLen = Len(strDescrip) - 2
If lngLen > 0 Then
strDescrip = "criteria: " & 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