Filter by List Box

  • Thread starter Thread starter Sheldon Penner
  • Start date Start date
S

Sheldon Penner

I need to filter a report by the Items Selected in a multi-
select listbox, that is, the user selects any number of
classes from the list, and the report returns statistics
(not a list, but the result of a rather complex summary
query) summarizing information about students in the
selected classes.

Is there any sample code lying around demonstrating this
sort of thing?

Thanks for any tips.
 
I hate writing the same code over and over like the code that loops through
the items selected in a multi-select list box. This function will accept a
list box control as an arguement and return syntax like:
" AND [ColorField] In ('Red', 'Black', 'Green') "
It expects a specific format of your list box name. If no items are selected
the function returns a zero length string. Save this function in a standard
module. Don't name the module the same as the function name.

A typical method of using this would be:
Dim strWhere as String
strWhere = " 1=1 "
strWhere = strWhere & BuildIn(Me.lboTColorField)
strWhere = strWhere & BuildIn(Me.lboNEmpID)
DoCmd.OpenReport "rptA", acViewPreview, , strWhere

Function BuildIn(lboListBox As ListBox) As String
'send in a list box control object
' the list box name must begin with _
"lbo" and be followed by one character describing the data type _
"T" for Text _
"N" for Numeric or _
"D" for Date and then the 5th characters on for the _
field name ie: lboEmployeeID
Dim strIn As String
Dim varItem As Variant
Dim strDelim 'used for delimiter
'Set the delimiter used in the IN (...) clause
Select Case Mid(lboListBox.Name, 4, 1)
Case "T" 'text data type
strDelim = "'" 'double quote
Case "N" 'numeric data type
strDelim = ""
Case "D" 'Date data type
strDelim = "#"
End Select

If lboListBox.ItemsSelected.Count > 0 Then
strIn = " AND [" & Mid(lboListBox.Name, 5) & "] In ("
For Each varItem In lboListBox.ItemsSelected
strIn = strIn & strDelim & lboListBox.ItemData(varItem) &
strDelim & ", "
Next
'remove the last ", " and add the ")"
strIn = Left(strIn, Len(strIn) - 2) & ") "
End If
BuildIn = strIn

End Function
 
Thank you for your response, Duane. The problem I am
having is not in constructing the "In ()" string but in
applying it as a filter to the report. How can I tell my
query or report to filter by the items selected?

-----Original Message-----
I hate writing the same code over and over like the code that loops through
the items selected in a multi-select list box. This function will accept a
list box control as an arguement and return syntax like:
" AND [ColorField] In ('Red', 'Black', 'Green') "
It expects a specific format of your list box name. If no items are selected
the function returns a zero length string. Save this function in a standard
module. Don't name the module the same as the function name.

A typical method of using this would be:
Dim strWhere as String
strWhere = " 1=1 "
strWhere = strWhere & BuildIn(Me.lboTColorField)
strWhere = strWhere & BuildIn(Me.lboNEmpID)
DoCmd.OpenReport "rptA", acViewPreview, , strWhere

Function BuildIn(lboListBox As ListBox) As String
'send in a list box control object
' the list box name must begin with _
"lbo" and be followed by one character describing the data type _
"T" for Text _
"N" for Numeric or _
"D" for Date and then the 5th characters on for the _
field name ie: lboEmployeeID
Dim strIn As String
Dim varItem As Variant
Dim strDelim 'used for delimiter
'Set the delimiter used in the IN (...) clause
Select Case Mid(lboListBox.Name, 4, 1)
Case "T" 'text data type
strDelim = "'" 'double quote
Case "N" 'numeric data type
strDelim = ""
Case "D" 'Date data type
strDelim = "#"
End Select

If lboListBox.ItemsSelected.Count > 0 Then
strIn = " AND [" & Mid(lboListBox.Name, 5) & "] In ("
For Each varItem In lboListBox.ItemsSelected
strIn = strIn & strDelim & lboListBox.ItemData (varItem) &
strDelim & ", "
Next
'remove the last ", " and add the ")"
strIn = Left(strIn, Len(strIn) - 2) & ") "
End If
BuildIn = strIn

End Function

--
Duane Hookom
MS Access MVP


I need to filter a report by the Items Selected in a multi-
select listbox, that is, the user selects any number of
classes from the list, and the report returns statistics
(not a list, but the result of a rather complex summary
query) summarizing information about students in the
selected classes.

Is there any sample code lying around demonstrating this
sort of thing?

Thanks for any tips.
Click to expand...


.
Click to expand...
 
Use the "where" clause of the DoCmd.OpenReport as suggested in the "typical
method of using" below.

--
Duane Hookom
MS Access MVP


Sheldon Penner said:
Thank you for your response, Duane. The problem I am
having is not in constructing the "In ()" string but in
applying it as a filter to the report. How can I tell my
query or report to filter by the items selected?

-----Original Message-----
I hate writing the same code over and over like the code that loops through
the items selected in a multi-select list box. This function will accept a
list box control as an arguement and return syntax like:
" AND [ColorField] In ('Red', 'Black', 'Green') "
It expects a specific format of your list box name. If no items are selected
the function returns a zero length string. Save this function in a standard
module. Don't name the module the same as the function name.

A typical method of using this would be:
Dim strWhere as String
strWhere = " 1=1 "
strWhere = strWhere & BuildIn(Me.lboTColorField)
strWhere = strWhere & BuildIn(Me.lboNEmpID)
DoCmd.OpenReport "rptA", acViewPreview, , strWhere

Function BuildIn(lboListBox As ListBox) As String
'send in a list box control object
' the list box name must begin with _
"lbo" and be followed by one character describing the data type _
"T" for Text _
"N" for Numeric or _
"D" for Date and then the 5th characters on for the _
field name ie: lboEmployeeID
Dim strIn As String
Dim varItem As Variant
Dim strDelim 'used for delimiter
'Set the delimiter used in the IN (...) clause
Select Case Mid(lboListBox.Name, 4, 1)
Case "T" 'text data type
strDelim = "'" 'double quote
Case "N" 'numeric data type
strDelim = ""
Case "D" 'Date data type
strDelim = "#"
End Select

If lboListBox.ItemsSelected.Count > 0 Then
strIn = " AND [" & Mid(lboListBox.Name, 5) & "] In ("
For Each varItem In lboListBox.ItemsSelected
strIn = strIn & strDelim & lboListBox.ItemData (varItem) &
strDelim & ", "
Next
'remove the last ", " and add the ")"
strIn = Left(strIn, Len(strIn) - 2) & ") "
End If
BuildIn = strIn

End Function

--
Duane Hookom
MS Access MVP


I need to filter a report by the Items Selected in a multi-
select listbox, that is, the user selects any number of
classes from the list, and the report returns statistics
(not a list, but the result of a rather complex summary
query) summarizing information about students in the
selected classes.

Is there any sample code lying around demonstrating this
sort of thing?

Thanks for any tips.
Click to expand...


.
Click to expand...
Click to expand...
 
Back
Top