Here are 3 ways to look at it.
Take your pick.
=============================
Here is a code snippet from a multi-select list box that builds a Where
clause:
For Each varItm In ctl.ItemsSelected
strWhere = strWhere & "(table.fieldname)='"
&Trim(ctl.ItemData(varItm)) & "' Or "
Next varItm
If strWhere = "" Then
MsgBox "Select one or more items."
Exit Sub
End If
strWhere = Left(strWhere, Len(strWhere) - 4)
strSQL = "SELECT .. "
strSQL = strSQL & "FROM ... "
strSQL = strSQL & "WHERE (" & strWhere & ");"
=======================================================
A Multi-Select ListBox can not be used directly in the Query Grid Criteria.
e.g. Forms![FormName]![ListBoxName] will NOT work.
Why?
Simply examine the output of the Selection and you will see that the string
produced is different from the one the Query Grid requires:
"MyText1 Or MyText2 Or MyText3" from Listbox
"MyText1" Or "MyText2" Or "MyText3" from query grid.
The solution to the multi-select listbox problem is to write the query in
code so that it looks the same way the query grid does.
Build the criteria based on selected values that you can determine by
looping through the selected property of the list box.
I usually write the query in the Grid and use multiple criteria and then
copy the SQL to code and work it into this format:
Private Sub btnCreateInvoice_Click()
On Error GoTo Err_btnCreateInvoice_Click
Dim frm As Form, ctl As Control, varItm As Variant, strCriteria As
String
Dim dbs As Database, qdf As QueryDef
Set dbs = CurrentDb
Set frm = Forms![CreateInvoiceData]
Set ctl = frm!ListPOs
strCriteria = ""
For Each varItm In ctl.ItemsSelected
strCriteria = strCriteria & "(pohdr.ponumber)='" &
Trim(ctl.ItemData(varItm)) & "' Or "
Next varItm
If strCriteria = "" Then
MsgBox "Select one or more PO's."
Exit Sub
End If
strCriteria = Left(strCriteria, Len(strCriteria) - 4)
strSQL = "SELECT [invnumber], Date() AS invdate, pohdr.vendno "
strSQL = strSQL & "FROM pohdr INNER JOIN poln ON pohdr.pokey =poln.pokey
"
strSQL = strSQL & "WHERE (" & strCriteria & ");"
If QueryExists("Inv1") = True Then
dbs.QueryDefs.Delete "Inv1"
End If
Set qdf = dbs.CreateQueryDef("Inv1")
qdf.SQL = strSQL
Set ctl = Nothing
Set frm = Nothing
Set dbs = Nothing
Exit_btnCreateInvoice_Click:
Exit Sub
Err_btnCreateInvoice_Click:
MsgBox ("Error # " & str(Err.Number) & " was generated by " & Err.Source
& Chr(13) & Err.Description)
Resume Exit_btnCreateInvoice_Click
End Sub
Function QueryExists(strQueryName As String) As Boolean
On Error Resume Next
QueryExists = IsObject(CurrentDb.QueryDefs(strQueryName))
End Function
To de-select all items in a list box try:
Dim lngX As Long
With Me![lstMyListBox]
For lngX = (.ItemsSelected.Count - 1) To 0 Step -1
.Selected(.ItemsSelected(lngX)) = False
Next lngX
End With
=======================================================
This uses a function MultiSelectSQL, from Graham.
Public Function MultiSelectSQL(ctl As Control, _
Optional Delimiter As String) As String
Dim sResult As String, vItem As Variant
With ctl
Select Case .ItemsSelected.Count
Case 0: sResult = " Is Null "
Case 1:
sResult = " = " & Delimiter & .ItemData(.ItemsSelected(0)) & Delimiter
Case Else
sResult = " in ("
For Each vItem In .ItemsSelected
sResult = sResult & Delimiter & .ItemData(vItem) & Delimiter & ","
Next vItem
Mid(sResult, Len(sResult), 1) = ")"
End Select
End With
MultiSelectSQL = sResult
End Function
Just paste the code above into a standard module.
If the bound column of your listbox is not numeric, you will need to pass a
double-quote as a delimiter. For example:
MultiSelectSQL( lstNames, """" )
--
Joe Fallon
Access MVP
Tim Burke said:
Any one try to pass a where clause into a saved query?
By this I mean, I have a query for a chart created. The user wants to
report on specific Zip codes. I created a Listbox with all values found in
a table. When the user goes to print, I parse the listbox selections into a
textbox that the query references in the where clause. For example:
"Select * from people where Zip IN (" & [Forms]![frmReports]![txtZips] &
")". This works when I select 1 zip, but when I select more than 1, it
returns an empty resultset.
Any suggestions?
Thanks!
Tim
