Filter not working for report

J

Julie B

Good morning! My problem is complicated to explain, I will try to be as clear
as possible.

I have a report containing a main section + 2 sub reports. The total of 3
sections all have different filters in the report filter. In a form, I have a
multi-select drop down to choose the desired departments to include in the
report.

When running the report, it looks like the multi-select works, but my
filters are not applied.

The following code is the "on click" event for a button to run the report :

On Error GoTo Err_cmdStrategic_Click

Dim varItem As Variant 'Selected items in lstApplication
Dim strWhere As String 'String to use as WhereCondition for
lstApplication
Dim strDescrip As String 'Description of WhereCondition for
lstApplication
Dim lngLen As Long 'Length of string for lstApplication
Dim strDelim As String 'Delimiter for this field type for
lstApplication.
Dim stDocName As String 'Name of 1st report to open.

stDocName = "MonthlyStrategic"

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

DoCmd.OpenReport stDocName, acViewPreview, WhereCondition:=strWhere

Exit_cmdStrategic_Click:
Exit Sub

Err_cmdStrategic_Click:
MsgBox Err.Description
Resume Exit_cmdStrategic_Click

Any ideas why the filters are not respected ??

Thanks so much for all replies !

Julie
 
J

Jeff L

I think your Open Report statement is wrong. It should be
DoCmd.OpenReport stDocName, acViewPreview, , strWhere

Hope that helps.
 
J

Julie B

Hi Jeff, thanks for your reply. I made the change you indicated but it did
not solve the proble. Same results.

Any other ideas ?

Thanks,

Julie


Jeff L said:
I think your Open Report statement is wrong. It should be
DoCmd.OpenReport stDocName, acViewPreview, , strWhere

Hope that helps.


Julie said:
Good morning! My problem is complicated to explain, I will try to be as clear
as possible.

I have a report containing a main section + 2 sub reports. The total of 3
sections all have different filters in the report filter. In a form, I have a
multi-select drop down to choose the desired departments to include in the
report.

When running the report, it looks like the multi-select works, but my
filters are not applied.

The following code is the "on click" event for a button to run the report :

On Error GoTo Err_cmdStrategic_Click

Dim varItem As Variant 'Selected items in lstApplication
Dim strWhere As String 'String to use as WhereCondition for
lstApplication
Dim strDescrip As String 'Description of WhereCondition for
lstApplication
Dim lngLen As Long 'Length of string for lstApplication
Dim strDelim As String 'Delimiter for this field type for
lstApplication.
Dim stDocName As String 'Name of 1st report to open.

stDocName = "MonthlyStrategic"

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

DoCmd.OpenReport stDocName, acViewPreview, WhereCondition:=strWhere

Exit_cmdStrategic_Click:
Exit Sub

Err_cmdStrategic_Click:
MsgBox Err.Description
Resume Exit_cmdStrategic_Click

Any ideas why the filters are not respected ??

Thanks so much for all replies !

Julie
Click to expand...
Click to expand...
 
J

Jeff L

Where is lstApplication given it's value? I don't see an assignment in
your code for that.

Are you sure that strWhere even has a value? Right before you Open
Report, put
Debug.Print "strWhere = " & strWhere
Open your report again by clicking the button on the form. Now go back
to your code window and hit Ctrl+G. You should get a window that opens
and it should say strWhere = blah blah blah.


Julie said:
Hi Jeff, thanks for your reply. I made the change you indicated but it did
not solve the proble. Same results.

Any other ideas ?

Thanks,

Julie


Jeff L said:
I think your Open Report statement is wrong. It should be
DoCmd.OpenReport stDocName, acViewPreview, , strWhere

Hope that helps.


Julie said:
Good morning! My problem is complicated to explain, I will try to be as clear
as possible.

I have a report containing a main section + 2 sub reports. The total of 3
sections all have different filters in the report filter. In a form, I have a
multi-select drop down to choose the desired departments to include in the
report.

When running the report, it looks like the multi-select works, but my
filters are not applied.

The following code is the "on click" event for a button to run the report :

On Error GoTo Err_cmdStrategic_Click

Dim varItem As Variant 'Selected items in lstApplication
Dim strWhere As String 'String to use as WhereCondition for
lstApplication
Dim strDescrip As String 'Description of WhereCondition for
lstApplication
Dim lngLen As Long 'Length of string for lstApplication
Dim strDelim As String 'Delimiter for this field type for
lstApplication.
Dim stDocName As String 'Name of 1st report to open.

stDocName = "MonthlyStrategic"

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

DoCmd.OpenReport stDocName, acViewPreview, WhereCondition:=strWhere

Exit_cmdStrategic_Click:
Exit Sub

Err_cmdStrategic_Click:
MsgBox Err.Description
Resume Exit_cmdStrategic_Click

Any ideas why the filters are not respected ??

Thanks so much for all replies !

Julie
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Add code to handle All jobs FROm dropdown list if None selected- 5
Crosstab Query Building Report 2
Works, But Doesn't Filter 1
select child records from listbox and generate a report 0
Error 13 - Type Mismatch 5
MultiListBox instead of Paramater query - Allen Browne code 7
Two Multi-Select List Boxes as Filters 11
list box selection as filter 1

Top