Concatenation operator within strings

[neilmber]

How can the concatenation operator be included within strings? I have a
function that concatenates the selected values from a listbox but would like
to add " & "*" Or " to each value:

Public Function ConcatList(frmWhichForm, varWhichControl)

Dim varItem As Variant
Dim stParameter As String
Dim WhichForm As Form
Dim WhichCntl As Control

Set WhichForm = frmWhichForm
Set WhichCntl = varWhichControl
stParameter = ""

For Each varItem In WhichCntl.ItemsSelected
stParameter = stParameter & WhichCntl.ItemData(varItem) & "
______________ "
Next varItem

If Len(stParameter) > "" Then
stParameter = Left$(stParameter, Len(stParameter) - 4)
End If

ConcatList = stParameter

End Function

Thanks.

 

[John W. Vinson]

How can the concatenation operator be included within strings? I have a
function that concatenates the selected values from a listbox but would like
to add " & "*" Or " to each value:

Public Function ConcatList(frmWhichForm, varWhichControl)

Dim varItem As Variant
Dim stParameter As String
Dim WhichForm As Form
Dim WhichCntl As Control

Set WhichForm = frmWhichForm
Set WhichCntl = varWhichControl
stParameter = ""

For Each varItem In WhichCntl.ItemsSelected
stParameter = stParameter & WhichCntl.ItemData(varItem) & "
______________ "
Next varItem

If Len(stParameter) > "" Then
stParameter = Left$(stParameter, Len(stParameter) - 4)
End If

ConcatList = stParameter

End Function

Thanks.

Just what are you trying to accomplish? You can certainly treat & as a
character within a string, with no problems. But it looks like you're trying
to build a SQL string to be used in an IN clause in a query; IN does not
recognize wildcards such as *.

Please explain the context. How will you be useing this concatenated list, and
what do you want the result to look like?

John W. Vinson [MVP]

 

[neilmber]

John,

Thanks. I am trying to build a WHERE clause. I'd like to have a form for
users to pick parameters from some listboxes for a series of reports. In
this case the parameters are building, floor, room, buyer, and cost center.
Users are likely to chose only one of the first three parameters (but
multiple selections within that parameter) and perhaps one or both of the
final two.

Neil

 

[John W. Vinson]

John,

Thanks. I am trying to build a WHERE clause. I'd like to have a form for
users to pick parameters from some listboxes for a series of reports. In
this case the parameters are building, floor, room, buyer, and cost center.
Users are likely to chose only one of the first three parameters (but
multiple selections within that parameter) and perhaps one or both of the
final two.

If you're building a SQL string, you can and should avoid using LIKE or
wildcards in cases where all records are wanted. Just build the WHERE clause
including only those controls where the user specified a criterion; if they
don't put in a criterion for the Cost Center, just don't include the Cost
Center in the criteria at all.

If you wish, you can post specifics of your code and the desired search.

John W. Vinson [MVP]