Below is a generic solution that I recommend. However, before you do
anything change your form on control names to something a bit more
professional. Tony Toews has some good resources at
http://www.granite.ab.ca/access/tablefieldnaming.htm.
I hate writing the same code over and over like the code that loops through
the items selected in a multi-select list box. This function will accept a
list box control as an arguement and return syntax like:
" AND [ColorField] In ('Red', 'Black', 'Green') "
It expects a specific format of your list box name. If no items are selected
the function returns a zero length string. Save this function in a standard
module. Don't name the module the same as the function name.
A typical method of using this would be:
Dim strWhere as String
strWhere = " 1=1 "
strWhere = strWhere & BuildIn(Me.lboTColor)
strWhere = strWhere & BuildIn(Me.lboNEmpID)
DoCmd.OpenReport "rptA", acViewPreview, , strWhere
Function BuildIn(lboListBox As ListBox) As String
'send in a list box control object
' the list box name must begin with _
"lbo" and be followed by one character describing the data type _
"T" for Text _
"N" for Numeric or _
"D" for Date and then the 5th characters on for the _
field name ie: lboEmployeeID
Dim strIn As String
Dim varItem As Variant
Dim strDelim 'used for delimiter
'Set the delimiter used in the IN (...) clause
Select Case Mid(lboListBox.Name, 4, 1)
Case "T" 'text data type
strDelim = "'" 'double quote
Case "N" 'numeric data type
strDelim = ""
Case "D" 'Date data type
strDelim = "#"
End Select
If lboListBox.ItemsSelected.Count > 0 Then
strIn = " AND " & Mid(lboListBox.Name, 5) & " In ("
For Each varItem In lboListBox.ItemsSelected
strIn = strIn & strDelim & lboListBox.ItemData(varItem) &
strDelim & ", "
Next
'remove the last ", " and add the ")"
strIn = Left(strIn, Len(strIn) - 2) & ") "
End If
BuildIn = strIn
End Function
You could also add arguments to the function that contain the Field Name and
Field Delimiter rather than storing these two pieces of information in the
listbox control name. The function might then look like
Function BuildIn(lboListBox As ListBox, _
strField as String, strDelimiter as String) _
As String
'etc
A call to the function could be:
strWhere = strWhere & _
(BuildInMe.lboColor, "Color", """" )
--
Duane Hookom
MS Access MVP
Ulcom said:
Hi
I have a query and I need to put a criteria on one of the field.
I have on a form "Form a" a listbox that list 12 different countries the
listbox is named "List 1"
When i put properties of "List 1" that you can select only one item, the
query works fine. I have put for criteria = [Forms]![Form a]![List 1]
So if i select Canada I get records matching. If I select France it works
fine also.
When I change property of the listbox so i can select many item in the
listbox Exemple: I pick Canada and France, then I obtain an empty query
and
it is wrong as I should get some records for Canada and some for France.
Anybody can give me a hint ?
thanks
