Concencate String from items in Multiselect List

[bymarce]

I'm trying to make a comma seperate list of the selected items in an unbound
multiselect list box to use as the subject of an email and rather than
returning "1983-0616" it's returning "8". The lstMLO rowsource is a text
field of format "####-####". How can I fix this? Thanks.
Marcie

Private Sub cmdEmailWork_Click()
Dim vItm As Variant 'Make the query to get the email addresses.
Dim stWhat As String 'Based on

'http://www.mvps.org/access/reports/rpt0005.htm
Dim stCriteria As String
Dim stSQL As String
Dim loqd As QueryDef
Dim lngLen As Long
Dim strMLO As String
Dim lngMLO As Long
Dim strOP As String
Dim strCP As String
Dim rptqd As QueryDef
Dim stSQLrpt As String
Dim stMySubj As String
Dim stCma As String
Dim ctl As Control

Set ctl = Me.lstMLO

stWhat = "": stCriteria = "Data.MLO =": stCma = ", "
If ctl.ItemsSelected.Count > 0 Then
For Each vItm In ctl.ItemsSelected
stWhat = strOP & stCriteria & ctl.ItemData(vItm) & strCP &
"OR "
stMySubj = ctl.ItemData(vItm) & stCma
Next vItm
stWhat = Len(stWhat) - 1
stMySubj = Len(stMySubj) - 2

Else: MsgBox "You have not selected any records."
GoTo ErrExit
End If

 

[Douglas J. Steele]

What's the bound column of your listbox?

ctl.ItemData(vItm) is going to return the value from the bound column of the
specific row, which may not be what's visible in the list box.

 

[bymarce]

There is only one column for the listbox. The SQL statement is:
SELECT DISTINCT Data.MLO FROM Data;

 

[Douglas J. Steele]

Try using

SELECT DISTINCT Format(Data.MLO, "####-####") FROM Data;

Setting a field's Format property only changes how it appears, not what's
actually stored in the table.

 

[bymarce]

Thanks. When I type that in it changes it to
SELECT DISTINCT Format(Data.MLO,"#-#") AS Expr1 FROM Data;

 

[bymarce]

I fixed the problem by removing the excess from my stWhat string rather than
creating a new string.
Marcie