Fill a field with items from a list seperated by comma's

[skateparkpages]

I have a list of items that I would like to appear in a report field but not as a list. I would like the items to appear consecutive seperated by a comma. Can any one provide a bit of code / point me in the right direction to solving this problem? Example:-

Source Info as follows:-
Hat
Cat
Mat
Rat

Report Output to be:-
Hat, Cat, Mat, Rat

Many thanks

 

[xlbo]

Well, if this is in excel, to assign to a variable, you could use

For each c in range("A1:A10&quot
if mStr = "" then
mStr = c.text
else
mStr = mStr & "," & c
end if
next
msgbox mStr Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde

 

[xlbo]

ooopos - forgot an important line:

mstr = ""
For each c in range("A1:A10&quot
if mStr = "" then
mStr = c.text
else
mStr = mStr & "," & c
end if
next
msgbox mStr Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde

 

[skateparkpages]

Sorry forgot to mention it is in Access. Can i still apply code or do i need to rweak it slightly.

Thanks for the reply

 

[xlbo]

Sorry - not very good on Access reports - have you tried asking this in any of the Access forums on this site ?? Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde

 

[SkipVought]

Hey skateparkpages,

Is your list in a Collection or an Array? Geoff's code can be used. I'ld suggest in a function...

Function MyString(MyArray) As String
    mStr = ""
    For i = LBound(MyArray, 1) To UBound(MyArray, 1)
        If mStr = "" Then
            mStr = MyArray(i)
        Else
            mStr = mStr & "," & c
        End If
    Next
    MyString = mStr
End Function

Hope this helps



Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[SkipVought]

oops...

Function MyString(MyArray) As String
    mStr = ""
    For i = LBound(MyArray, 1) To UBound(MyArray, 1)
        If mStr = "" Then
            mStr = MyArray(i)
        Else
            mStr = mStr & "," & MyArray(i)
        End If
    Next
    MyString = mStr
End Function

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[skateparkpages]

The list of items are created from a query in Access

Query Results currently reads:-
Column1 Column2
Rabbit Bunny
Rabbit Hole
Rabbit Tail
Deer Antlers
Goat Billy
Goat Horns

Want it to read after GROUP BY

Column1 Column2
Rabbit Bunny, Hole, Tail
Deer Antlers
Goat Billy, Horns

Sorry about the examples!!!!!


Tried code previously supplied using expression builder in the query but kept getting a runtime error in line starting
For i =
Hope the example above clarifies what I am trying to achieve.

Thanks

 

[xlbo]

I really would suggest asking this in one of the access forums Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde

 

[TonyJollans]

Hi,

I don't think you can do this directly but the following should give you a starting point.

Create your report with the Record Source set to:
"SELECT DISTINCT Column1 FROM YourQuery"

Put two text boxes on it.
Set the Control Source for the first to "Column1",
and for the second to "=YourFunc(Column1)".

Now create a new Module (or add to an existing one if you wish) and code a function like

Function YourFunc(Col1 As String) As String

Dim Selection As String
Dim Details As Recordset

Selection = "SELECT Column2 FROM YourQuery" & _
            " WHERE Column1 = """" & Col1 & """"

Set Details = CurrentDb.OpenRecordset(Details, dbOpenDynaset)

YourFunc = ""
Do While Not Details.Eof
    YourFunc = YourFunc & Details!Column1 & ", "
    Details.MoveNext
Loop
YourFunc = left(YourFunc, Len(YourFunc)-2)

Details.Close

End Function

Enjoy,
Tony