Multiselect List Box Problem

[aliendan]

Here’s another one…those darned list boxes again. This time I’m working with a multiselect list box (this is a new one for me). Running a querydef from the SQL I’m wanting to populate the criteria in a field in the query with the Block ID numbers of the Blocks chosen from lstBlck. This will show data about the selected Blocks. Of course, in the criteria., if more than one is selected the query will need an “Or Like” in between the Block ID numbers. For instance, if I select two blocks and the code runs it tries to put “8 Or Like 51 Or Like” (strList equals this) into the query as the criteria. This is no good. It needs to place … 8 Or Like 51 into the criteria instead. Can anyone tell me how to rid the result of "strList" of the quotations and the trailing “Or Like”?

In other words:
strList = “8 Or Like 51 Or Like”
It needs to be this:
strList = 8 Or Like 51

Here is the code I’m using:

Private Sub lstBlck_AfterUpdate()
Dim db As DAO.Database
Dim strSQL As String
Dim intI As Integer
Dim ctlBlck As Control
Dim strList As String

Set ctlBlck = [Forms]![frmWllRprtFltr]![lstBlck]

With ctlBlck
If .MultiSelect = 0 Then
txtselected = .Value
Else
For Each varItem In .ItemsSelected
strList = strList & .Column(0, varItem) & " " & "Or Like" & " "
Next varItem
End If
End With

strSQL = ""
strSQL = strSQL & "SELECT tblpklstSctn.SctnID, tblpklstSctn.SctnBlckID, "
strSQL = strSQL & "tblpklstSctn.SctnNmbr "
strSQL = strSQL & "FROM tblpklstSctn "
strSQL = strSQL & "WHERE (((tblpklstSctn.SctnBlckID)=" & strList & &quot); "

Set db = CurrentDb

For intI = 0 To db.QueryDefs.Count - 1
If db.QueryDefs(intI).Name = "qrylstSctn" Then
db.QueryDefs(intI).SQL = strSQL
End If
Next intI

End Sub
[sig]<p>Dan Rogotzke<br><a href=mailto:ddrogotzke@alturaenergy.com>ddrogotzke@alturaenergy.com</a><br>[/sig]

 

[MichaelRed]

If (Right(strList, 8) = &quot;Or Like &quot Then

[tab]strList = Left(strList, Len(strList) - 8)

End If

After the While Loop --- BUT count the chars again --- my fingers are tired on Fri afternoons!!

[sig]<p>MichaelRed<br><a href=mailto:mred@duvallgroup.com>mred@duvallgroup.com</a><br>There is never time to do it right but there is always time to do it over[/sig]

 

[aliendan]

Sorry I didn't get back to ya earlier..been busy finishing the project. Anyway, that's what I needed. Thank you! With a little manipulation here and there everything worked great. [sig]<p>Dan Rogotzke<br><a href=mailto:ddrogotzke@alturaenergy.com>ddrogotzke@alturaenergy.com</a><br>[/sig]