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
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: