filtering a report

[Guest]

I have a form with two multi-select listboxes with employees and skills.
Each has a view report button. When the employees are selected it shows all
of the skills and their skill level ( novice, no experience, etc.) I want to
exclude the skills with no experience. Also, for the skills report, it shows
all of the employees with that skill. I want to exclude the employees with no
experience and I want skill level to show in a specific order ( expert,
intermediate,novice). How can I do this. I used Allen Browne's code for
multi-select listbox to filter a report. How can I add additional filters to
that code or directly to the report.

 

[OfficeDev18 via AccessMonster.com]

DoCmd.OpenReport "your report name here",,,"put your filter here in SQL form,
but without the SQL keyword WHERE"

See the OpenReport method in the help file for details.

Sam

 

[Guest]

I tried adding, "skill_level <> "No experience" but I got an error message
saying Error 13 Type Mismatch. skill_Level is a field, is that the problem.

 

[OfficeDev18 via AccessMonster.com]

Try changing your quotes to: "skill_level <> 'No experience'"

that is, after the <> signs, <space><single quote>No experience<single
quote><double quote>

SQL is ticklish when it comes to proper quote usage.

By the way, if this doesn't work, would you mind doing a copy-and-paste on
your entire DoCmd statement so we can get a better idea of what Access is
seeing?

Thanks,

Sam

DoCmd.OpenReport "your report name here",,,"put your filter here in SQL form,
but without the SQL keyword WHERE"

[quoted text clipped - 12 lines]

 

[Guest]

It didn't work, so here is the code:

Private Sub cmdPreview_Click()
On Error GoTo Err_Handler

Dim varItem As Variant
Dim strWhere As String
Dim strDescrip As String
Dim lngLen As Long
Dim strDelim As String
Dim strDoc As String

strDelim = """"
strDoc = "Employee"


With Me.Employee
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then

strWhere = strWhere & strDelim & .ItemData(varItem) &
strDelim & ","

strDescrip = strDescrip & """" & .Column(1, varItem) & """, "
End If
Next
End With


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


If CurrentProject.AllReports(strDoc).IsLoaded Then
DoCmd.Close acReport, strDoc
End If


DoCmd.OpenReport strDoc, acViewPreview, WhereCondition:=strWhere And
"skill_level <> 'No experience'", OpenArgs:=strDescrip

Exit_Handler:
Exit Sub

Err_Handler:
If Err.Number <> 2501 Then
MsgBox "Error " & Err.Number & " - " & Err.Description, ,
"cmdPreview_Click"
End If
Resume Exit_Handler


End Sub

Try changing your quotes to: "skill_level <> 'No experience'"

that is, after the <> signs, <space><single quote>No experience<single
quote><double quote>

SQL is ticklish when it comes to proper quote usage.

By the way, if this doesn't work, would you mind doing a copy-and-paste on
your entire DoCmd statement so we can get a better idea of what Access is
seeing?

Thanks,

Sam

DoCmd.OpenReport "your report name here",,,"put your filter here in SQL form,
but without the SQL keyword WHERE"

[quoted text clipped - 12 lines]

multi-select listbox to filter a report. How can I add additional filters to
that code or directly to the report.

 

[OfficeDev18 via AccessMonster.com]

I was afraid of that. To this statement,

DoCmd.OpenReport strDoc, acViewPreview, WhereCondition:=strWhere And
"skill_level <> 'No experience'", OpenArgs:=strDescrip

you need to add two commas, because the position of arguments is critical in
VBA. The correct statement is

DoCmd.OpenReport strDoc, acViewPreview, , WhereCondition:=strWhere And
"skill_level <> 'No experience'", , OpenArgs:=strDescrip

That is, add another comma after the View argument to accommodate the missing
Filter Name argument (the WhereCondition is a literal, not an object name),
and add another comma after the WhereCondition to accommodate the missing
Window argument. Now try it again.

By the way, I think that the books say you don't need place commas when you
use WhereCondition:= and OpenArgs:=, but if it's not working anyhow, maybe
you should 1) try it with the commas, and/or 2) use the format that doesn't
include these forms.

You might want to visit the OpenReport method in the help file.

If this doesn't work, you might want to set a stop point in the program on
the line of code before the .OpenReport statement, and analyze the value of
strWhere at that point, even though you have more to add to it. I'm wondering.
...

Sam

It didn't work, so here is the code:

Private Sub cmdPreview_Click()
On Error GoTo Err_Handler

Dim varItem As Variant
Dim strWhere As String
Dim strDescrip As String
Dim lngLen As Long
Dim strDelim As String
Dim strDoc As String

strDelim = """"
strDoc = "Employee"


With Me.Employee
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then

strWhere = strWhere & strDelim & .ItemData(varItem) &
strDelim & ","

strDescrip = strDescrip & """" & .Column(1, varItem) & """, "
End If
Next
End With


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


If CurrentProject.AllReports(strDoc).IsLoaded Then
DoCmd.Close acReport, strDoc
End If


DoCmd.OpenReport strDoc, acViewPreview, WhereCondition:=strWhere And
"skill_level <> 'No experience'", OpenArgs:=strDescrip

Exit_Handler:
Exit Sub

Err_Handler:
If Err.Number <> 2501 Then
MsgBox "Error " & Err.Number & " - " & Err.Description, ,
"cmdPreview_Click"
End If
Resume Exit_Handler

End Sub

Try changing your quotes to: "skill_level <> 'No experience'"

[quoted text clipped - 19 lines]