Walk Multi Select List Box A2003

Hi All

I am using standard code to walk through a multi select list boxes and
build a sql string to filter data, I would also like to store the 2nd
column value for each selected item as well in say strFilter, but seem
to have trouble with the correct syntax / column property

For Each strItem In cntl.ItemsSelected
strSQL = strSQL & cntl.ItemData(strItem) & " Or [staStatusID]= "

Next strItem


Thanks for any help


Steve G

SKG wrote:
[color=blue]
> Hi All
>
> I am using standard code to walk through a multi select list boxes and
> build a sql string to filter data, I would also like to store the 2nd
> column value for each selected item as well in say strFilter, but seem
> to have trouble with the correct syntax / column property
>
> For Each strItem In cntl.ItemsSelected
> strSQL = strSQL & cntl.ItemData(strItem) & " Or [staStatusID]= "
>
> Next strItem
>
>
> Thanks for any help
>
>
> Steve G
>[/color]

Not sure I follow. This maight help...
Dim var As Variant
Dim strFilter As String
If me.listbox0.ItemsSelected.Count > 0 then
For Each var In Me.ListBox0.ItemsSelected
'get the second column value in listbox
strFilter = strFilter & Me.ListBox0.Column(1,var) & ", "
next var
'remove comma and add ()
strFilter = "Where [staStatusID]IN " & _
(" & left(strFilter,len(strFilter)-2) & ")"
endif

This might end up like
Where [staStatusID]IN (1,2,3,4,5)

Now you can add the filter to the SQL statement.
strSQL = "Select * From Emp " & strFilter & " Order By EmpName

Hi Salad

Yep Thanks for the ideas, just me not thinking very straight. Amended
to read....


For Each strItem In cntl.ItemsSelected
strSQL = strSQL & cntl.ItemData(strItem) & " Or [staStatusID]= "
strFilter = strFilter & cntl.Column(1, strItem) & ", "
Next strItem


A bit of tidying up at the end to get rid of any unwanted spaces &
commas etc etc and easy as you like....

Thanks again for your help in pointing me in the right direction

Cheers

Steve G