Need Function to merge list box values

  • Thread starter Thread starter Shree
  • Start date Start date
S

Shree

Hi,

I am creating a database for my process and we get multiple seal numbers for
each transactions. I need to list out all the seal numbers in one row while
preparing a report.
Can any one help me to create a function which will take the values from a
list box and displays all the records in one single text box.
 
Shree,

If you mean concatenate the selected values from a listbox into a single
string, it would look something like:

Public Function fnMultiList(lst As Control) As Variant

Dim varList As Variant
Dim varItem As Variant

varList = Null
For Each varItem In lst.ItemsSelected
varList = (varList + ", ") & lst.ItemData(varItem)
Next

fnMultiList = varList

End Function

This function works for numeric values in the list. If the bound column of
your list contains string values then replace:

varList = (varList + ", ") & lst.ItemData(varItem)

with

varList = (varList + ", ") & chr$(34) & lst.ItemData(varItem) & chr$(34)

Generally, I would call this function in the click event of a button on the
form, where I build a SQL string dynamically. Something like:

strSQL = "SELECT * FROM yourTable" _
& ("WHERE [SomeField] IN (" + fnMultiList(me.lstName) + ")" )

By formatting it this way, the WHERE clause will be ignored if no values are
selected in the list.

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.
 
Thank you Dale,

Dale Fye said:
Shree,

If you mean concatenate the selected values from a listbox into a single
string, it would look something like:

Public Function fnMultiList(lst As Control) As Variant

Dim varList As Variant
Dim varItem As Variant

varList = Null
For Each varItem In lst.ItemsSelected
varList = (varList + ", ") & lst.ItemData(varItem)
Next

fnMultiList = varList

End Function

This function works for numeric values in the list. If the bound column of
your list contains string values then replace:

varList = (varList + ", ") & lst.ItemData(varItem)

with

varList = (varList + ", ") & chr$(34) & lst.ItemData(varItem) & chr$(34)

Generally, I would call this function in the click event of a button on the
form, where I build a SQL string dynamically. Something like:

strSQL = "SELECT * FROM yourTable" _
& ("WHERE [SomeField] IN (" + fnMultiList(me.lstName) + ")" )

By formatting it this way, the WHERE clause will be ignored if no values are
selected in the list.

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.



Shree said:
Hi,

I am creating a database for my process and we get multiple seal numbers for
each transactions. I need to list out all the seal numbers in one row while
preparing a report.
Can any one help me to create a function which will take the values from a
list box and displays all the records in one single text box.
Click to expand...
Click to expand...
 
Back
Top