I recently just worked on something like this & it worked out great. I read the article mentioned by Pat and figured I could still add to it.
In addition to a list box i used a dropdown to select a field. The listbox was then populated with a distinct selection from that field. So, given that i decided to use this idea I went with building sql on the fly as apposed to passing parameters to a query. It gave the flexibility to change just about everything at run time.
Take a look at the code below which will need some work but will give you some ideas:
Function BUILD_SELECT(strWhere As String, Ctl As Control, strFieldz As String)
Dim intCount As Integer
Dim strWhereCurrent As String
Dim varSelections As Variant
Dim varItem As Variant
Dim varWheee As Variant
Dim x As String
Debug.Print "**********BEGIN--BUILD_SELECT************"
'Debug.Print "Field Type: " & str(FieldType(strObjectName, strFieldz))
'BASED ON THE FIELD PASSED TO THIS FUNCTION, CREATE THE CORRECT BORDERS FOR THE ITEM
Select Case FieldType(strObjectName, strFieldz)
Case 1 To 7
x = ""
Case 8
x = "#"
Case Else
x = """"
End Select
'CHECK NUMBER OF ITEMS SELECTED TO SEE IF SELECT SHOULD BE BUILT
Set varWheee = Ctl.ItemsSelected
If varWheee.Count > 0 Then
'YES, IT SHOULD BE BUILT
'Debug.Print "TRUE: varWheee.Count > 0 -- value is: " & varWheee.Count
For Each varSelections In Ctl.ItemsSelected
intCount = intCount + 1
varItem = Ctl.ItemData(varSelections)
'Debug.Print "varItem: " & intCount & "{" & varItem & "}"
strWhereCurrent = "(" & strObjectName & "." & strFieldz & "=" & x & varItem & x & ")"
If intCount > 20 Then
MsgBox "20 is the Maximum number of selections allowed from one box", vbOKOnly + vbCritical
BUILD_SELECT = ""
Exit Function
ElseIf intCount > 1 Then
strWhere = strWhere & " or " & strWhereCurrent
Else
strWhere = strWhereCurrent
End If
Next varSelections
BUILD_SELECT = " AND (" & strWhere & ")"
Else
'NO, IT SHOULD NOT BE BUILT
'Debug.Print "TRUE: varWheee.Count is NOT > 0 -- Value is: " & varWheee.Count
BUILD_SELECT = ""
End If
Debug.Print "**********END----BUILD_SELECT************"
Some points to make about it:
Fieldtype is a function which takes the object name and field name and returns the field type by looping through the querydef until it finds that field, getting it's field type. you could also hard code it though. It is needed to decide what type of borders to put around parameters... ie. nothing for numbers, # for dates etc.
Maximum number of comparisons is somewhere around 40 in access. this function only allows 20.
This function only writes the where clause. You would need to put it in the filter property of a report.
I had a great time writing this, hope it helps.
norm
