Hi Peter
(Warning Untested air code)
** Code Start**
Dim varItems as Variant
Dim strSelect as String
Dim strWhere as String
Dim strSQL as String
Dim strMods as String
strSelect = "SELECT * FROM YourTable "
For Each varItems In YourForm!YourListBox.ItemsSelected
If strMods = vbNullString Then
strMods = Chr(39) & YourForm!YourListBox.ItemData(varItems) &
Chr(39)
Else
strMods = strMods & ", " & Chr(39) &
YourForm!YourListBox.ItemData(varItems) & Chr(39)
End If
Next
strWhere = "WHERE module In (strMods);"
strSQL = strSelect & strWhere
** End Code **
Chr(39) is the apostrophe so it could be shortened to "'" That is a double
quote then apostrophe then double quote
Yes this has to done in VBA. You might have to error trap the code to
prevent it running if there are no items are selected in your list box.
Although this will generate the sql for a query, how are you going to
run/view the query? Are you going to create a temporary query? Are you using
this for a recordsource for a form or report?
Jeff
"Peter Bailey" <pe*********@andaluz.fsbusiness.co.uk> wrote in message
news:ch**********@newsg4.svr.pol.co.uk...
I have a list box on a form that will contain a list of course modules for
the school where I work. I want to allow the user to select single or
multiple modules ie Modue M10S and M10SA. what I want to do is create a
dynamic sql statement that will have something like:
Where tblx.module = "M10S" And "M10SA"
question one: how do I extract the string from the list box if more than
one item is selected, (I assume it may have some kind of count for the number
of items selected?)
question 2 : I assume I then need to use this count in the VBA to build up
the qry String?
regards in advance.
Peter