Multiple selections on list boxes as query criteria

  • Thread starter Thread starter Roberta Walker
  • Start date Start date
R

Roberta Walker

I wand to use a list box to make multiple selections to
use as query criteriam, but I dont seem to be getting on
very well!

IS this possible and if so how please
 
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.lboTColor)
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

You could also add arguments to the function that contain the Field Name and
Field Delimiter rather than storing these two pieces of information in the
listbox control name. The function might then look like

Function BuildIn(lboListBox As ListBox, _
strField as String, strDelimiter as String) _
As String
'etc
A call to the function could be:

strWhere = strWhere & _
(BuildInMe.lboColor, "Color", """" )
 
How would you call the BuildIn function from within the query criteria

If for instance I have a list box called [forms]![frmAvailability]![lboTQuarterAssigned]
do I pass call the function a

buildIn("lboTQuarterAssigned") o

buildIn([forms]![frmAvailability]![lboTQuarterAssigned]) o

buildIn("[forms]![frmAvailability]![lboTQuarterAssigned]"

and alter the offsets used in the string functions within your code accordingly (if I use method 2 or 3)?
 
Sorry Duan

Can you disregard the last pos

I've used your code and it's working to a point. The text string that it is producing is excatly right in all ways except when I use in in the query criteria, I'm getting an empty recordset. When I hard code the query criteria, it works fine, even though it appears to be the identical result of your code. As a test, I converted the hard coded query criteria to a string and right enough, I get an empty recordset so it appears that the datatype of the returned value from your code is the problem i.e. it is a string and this is being matched for rather than the criteria expression that it should be getting matched for

Any ideas?
 
Back
Top