I'm trying to build an SQL string that uses a multi-select listbox as
its parameters...but, I'm not sure how to do it. I know how to create
a variable and assign it to the value of a combo box, for example.

How do I use a multi-select's listbox multiple values as multiple
parameters for an SQL string?

TIA...

This is untested and assumes something is selected in the list but should
give you the general idea. I've assumed a string equivalent field type,
hence the single quotes around the items.

Dim lCounter as Long
Dim strSQL as string

strSQL = "SELECT * FROM MyTable Where FieldName IN ("

With lstBox
For lCounter= 0 to .ListCount - 1
if .Selected then
If Right(strSQL,1) = "'" Then strSQL = strSQL & ","
strSQL = strSQL & "'" & .List(.Listindex) & "'"
End If
Next LCounter
End With
strSQL = strSQL & ")"

Robin Hammond
www.enhanceddatasystems.com

"John" wrote in message
om...
I'm trying to build an SQL string that uses a multi-select listbox as
its parameters...but, I'm not sure how to do it. I know how to create
a variable and assign it to the value of a combo box, for example.

How do I use a multi-select's listbox multiple values as multiple
parameters for an SQL string?

TIA...

Robin, does your code use a multi-select list box? I keep getting "not
optional" errors on the "IF .selected then" statement.

I'm not too familar with Excel forms (I've done mostly Access
programming), and I'm having problems with the multi-select list box
as a parameter "builder".

"Robin Hammond" wrote in message ...
This is untested and assumes something is selected in the list but should
give you the general idea. I've assumed a string equivalent field type,
hence the single quotes around the items.

Dim lCounter as Long
Dim strSQL as string

strSQL = "SELECT * FROM MyTable Where FieldName IN ("

With lstBox
For lCounter= 0 to .ListCount - 1
if .Selected then
If Right(strSQL,1) = "'" Then strSQL = strSQL & ","
strSQL = strSQL & "'" & .List(.Listindex) & "'"
End If
Next LCounter
End With
strSQL = strSQL & ")"

Robin Hammond
www.enhanceddatasystems.com

"John" wrote in message
om...
I'm trying to build an SQL string that uses a multi-select listbox as
its parameters...but, I'm not sure how to do it. I know how to create
a variable and assign it to the value of a combo box, for example.

How do I use a multi-select's listbox multiple values as multiple
parameters for an SQL string?

TIA...

Sorry, as I said, it was done on the fly. It should be

If .Selected(lCounter)

Robin Hammond
www.enhanceddatasystems.com

"John" wrote in message
om...
Robin, does your code use a multi-select list box? I keep getting "not
optional" errors on the "IF .selected then" statement.

I'm not too familar with Excel forms (I've done mostly Access
programming), and I'm having problems with the multi-select list box
as a parameter "builder".

"Robin Hammond" wrote in message
...
This is untested and assumes something is selected in the list but
should
give you the general idea. I've assumed a string equivalent field type,
hence the single quotes around the items.

Dim lCounter as Long
Dim strSQL as string

strSQL = "SELECT * FROM MyTable Where FieldName IN ("

With lstBox
For lCounter= 0 to .ListCount - 1
if .Selected then
If Right(strSQL,1) = "'" Then strSQL = strSQL & ","
strSQL = strSQL & "'" & .List(.Listindex) & "'"
End If
Next LCounter
End With
strSQL = strSQL & ")"

Robin Hammond
www.enhanceddatasystems.com

"John" wrote in message
om...
I'm trying to build an SQL string that uses a multi-select listbox as
its parameters...but, I'm not sure how to do it. I know how to create
a variable and assign it to the value of a combo box, for example.

How do I use a multi-select's listbox multiple values as multiple
parameters for an SQL string?

TIA...