Try this...this is a function that will build a where clause based on selections of a list box. You can use this where clause as part of a SQL statement, or you can use it as a filter when opening reports/forms.
Just plug it in to a module, and call it passing in the paramaters. If you have any questions over its use, please let me know.
jeff
'****BEGIN CODE****PASTE INTO MODULE
'***********************************************************************************
'*** Build Where Clause
'***
'*** Description
'*** Builds the where clase for an SQL statement based on items selected in a list
'*** box (with multiselect set to true) which uses an OR operator
'***
'*** Parameters
'*** pstrFormName - the form name where the list box calls home
'*** pstrCurrentListBox - The list box name that contains the items for the SQL statement
'*** pstrFieldName - The field name used in the Query that the items are compared to
'*** Returns
'*** Returns the where clause.
'*** Example Call:
'*** strSQL = "Select * from tblMain WHERE " & BuildWhereClause (frmPeople, lstItems, "IDNumber"

'*** Returns
'*** "IDNumber = 1 OR IDNumber = 2 OR IDnumber = 3;"
'*** value of strSQL:
'*** "Select * from tblMain WHERE IDNumber = 1 OR IDNumber = 2 OR IDnumber = 3;"
'*** Procedures called
'***
'*** Revisions
'*** Created 4-13-1999 - Jeff Hammond jthammond@onebox.com
'***********************************************************************************
Public Function BuildWhereClause(ByVal pstrFormName As String, _
ByVal pstrCurrentListBox As String, _
ByVal pstrFieldName As String) As String
Const MaxSelection As Integer = 15 'Maximum number of items
On Error GoTo ErrorHandler
Dim varItemSelected As Variant
Dim astrItemsToRemove(MaxSelection) As String
Dim intItemCounter As Integer 'Used to track number in array
Dim intLastArrayItem As Integer 'Last item in array
'Set initial variables
intItemCounter = -1
For Each varItemSelected In Forms(pstrFormName).Controls(pstrCurrentListBox).ItemsSelected
If intItemCounter < MaxSelection - 1 Then
intItemCounter = intItemCounter + 1
astrItemsToRemove(intItemCounter) = Forms(pstrFormName).Controls(pstrCurrentListBox).ItemData(varItemSelected)
Else
MsgBox "Only " & MaxSelection & " items can be selected. Only the first " & MaxSelection & " items selected will be used.", vbInformation, "Error..."
Exit For
End If
Next varItemSelected
'Set the last item in the array
intLastArrayItem = intItemCounter
'build SQL statement where clase for all but last item in array (if only 1 item, is skipped)
For intItemCounter = 0 To intLastArrayItem - 1
BuildWhereClause = BuildWhereClause & pstrFieldName & " = " & astrItemsToRemove(intItemCounter) & " OR "
Next intItemCounter
'Add last itme in array to SQL statement
'BuildWhereClause = BuildWhereClause & pstrFieldName & " = " & astrItemsToRemove(intLastArrayItem) & ";"
BuildWhereClause = BuildWhereClause & pstrFieldName & " = " & astrItemsToRemove(intLastArrayItem)
Exit Function
ErrorHandler:
Select Case Err.Number
Case 9 'sub script out of range
Resume Next
Case Else
MsgBox Err.Number & Chr(10) & Err.Description
End Select
End Function