Navigation:  Articles > Aug-1997 >

Tip: The IN Crowd

Previous pageReturn to chapter overviewNext page

Ken Getz


You've given your users a multi-select box and they've selected the items they want. This code turns their selection into a SQL Where clause so you can retrieve the items they've asked for. The IN keyword works nicely here because it matches all the items in a list (such as IN("item1", "item2", . . .)). The code creates the necessary input by walking the ItemsSelected collection of the list box, tacking every element in the property onto a string, and putting quotes around it:


Function BuildIn(lstItems As Control, _

  strFieldName As String) As String

  Dim varItem As Variant

  Dim strOut As String

  Const conQuote = """"


  If lstItems.ItemsSelected.Count = 0 Then

    BuildIn = ""


    For Each varItem In lstItems.ItemsSelected

      strOut = strOut & "," & conQuote & _

         lstItems.ItemData(varItem) & conQuote

    Next varItem

    ' Get rid of leading comma.

    BuildIn = " WHERE " & strFieldName & _

      " IN (" & Mid$(strOut, 2) & ")"

  End If

End Function



Then, calling this function and passing in a reference to the list box, it should return a string containing something like "WHERE CustomerID IN ("Item1","Item2","Item3")". You could tag this onto your SQL like this (assuming that strSQL already contains something like "SELECT * FROM tblCustomers"):


 strSQL = strSQL & _

   BuildIn(Me!lstCustomerIDS, "CustomerID")



Now strSQL will contain "SELECT * FROM tblCustomers WHERE CustomerID IN ("Item1","Item2","item3")".

If your items in the list box contain quotes (that is, the double-quote character), this will fail, and you'll need to find some other way to delimit the strings from the list box.

See More On Listboxes

Let me check my list…

Your Listbox-Filling Options


We are good at Access and Excel Programming and UpgradesLoad Basecamp Backup XML to Access or SQLServerThe Smart Access PDFs and DownloadsThe Toolbox - Access KBData Mining Tool In AccessThe Access WorkbenchThese are the products and services that we sell