Good afternoon.
I have a form (selector) on which there is a multiselect list box (techselect). A command button takes the selections highlighted in the list box, and concatenates them for use in a query. The query's CRITERIA is set up as follows, to receive those selections:
In ([forms]![selector]![resultsfromtechchoices])
The code that creates the concatenated string is at the bottom of this posting.
The odd thing is that if I choose one item from the listbox, it works fine. If I choose two, the string is concatenated properly, but the query returns no records. If I cut the results from the text box, and paste them into the query criteria directly, it works properly. Therefore I feel I'm certain the concatenation is being done properly.
I have tried every combination of single quotes, double quotes, spacing, commas, yaddah yaddah.
Any thoughts would be greatly appreciated.
Stephen.
Total newbie. This is my first attempt at any code, so don't slap me too hard!
---------------
Private Sub Command2_Click()
Dim varitem As Variant
Dim strcriteria As String
Dim strSQL As String
For Each varitem In Me!Techselect.ItemsSelected
strcriteria = strcriteria & Me!Techselect.ItemData(varitem) & ", "
Next varitem
If Len(strcriteria) = 0 Then
MsgBox "you did not select anything from the list of technicians", vbExclamation, "Nothing to find!"
Exit Sub
End If
strSQL = left(strcriteria, Len(strcriteria) - 2)
If Len(strSQL) > 0 Then
strSQL = left(strSQL, Len(strSQL))
Me.ResultsFromTechChoices.Value = strSQL
Else
Me.ResultsFromTechChoices = Null
End If
End Sub