Multi-select listbox help!
 

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...

Multi-select listbox help!
 

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...

Multi-select listbox help!
 

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...

Multi-select listbox help!
 

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...