Tip: The IN Crowd

<< Click to Display Table of Contents >>

Navigation:  Smart Access 1996-2006 > Aug-1997 > Seek and Ye Shall Find . . . >

Tip: The IN Crowd

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 = ""

  Else

    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