Listbox

[philippe_b]

Hi !

Got a small problem, I have a datebase for UK contacts with UK
postcodes.
eg: "GU35 9LW"

As the first two letters indicates an area, I want to be able to use a
listbox to search in my query these first two letters, one of them or
any combination.
I did a first query to extract these first two letters then I created a
form with a multiple selection listbox. As the result of the listbox
cannot be use as such, I have a textbox showing the result of my
selection using this code:

Private Sub List17_AfterUpdate()

Dim varItem
Me.Text22 = ""
For Each varItem In Me.List17.ItemsSelected
If Len(Me.Text22) = 0 Then
Me.Text22 = """" & Me.List17.ItemData(varItem) & """"
Else
Me.Text22 = Me.Text22 & "," & """" &
Me.List17.ItemData(varItem) & """"
End If
Next varItem

End Sub

At the end my problem is that I cannot have the In(..) function
working.I also tried using the OR operator but still the same problem.

If anyone can help.

 

[Arvin Meyer [MVP]]

The trick is to use a textbox to hold the members of the array:

With Me!lstElevation
If .MultiSelect = 0 Then
Me!txtSelected = .Value
Else
For Each varItem In .ItemsSelected
strList = strList & .Column(0, varItem) & ","
Next varItem
If strList <> "" Then
strList = Left$(strList, Len(strList) - 1)
End If
Me.txtSelected = strList
End If

End With

txtSelected is nothing more than a hidden textbox which holds the comma
separated list. Then in your IN clause just use the textbox reference:

In (" & Me.txtSelected & ")

 

[philippe_b]

First, thank you for helping Arvin.
I've just tried your solution but the problem is the same, the query
does not give any records with the following criteria. I also tried
with OR operators instead of the comma but it is the same.

 

[Arvin Meyer [MVP]]

I can't understand why you are having that problem. The exact code I posted
has been running for the last 6 years without any problems. If you use the
OR operator, you no longer need the IN clause.