List Results Separated by Commas on Report 1

[isorders]

I have results from a query that I want to appear on a report separated by commas.
qryResults
a
b
c
d
e
Report Field = a, b, c, d, e
Thanks!!

 

[WantsToLearn]

A query can call a function as it is running if it occurs in a stand-a-lone standard module. Try something like this and then put the final result in the report footer:

Public Function BuildString(QueryRowName As Var) As String
Static strResult As String 'Statics retain their value
strResult = strResult & QueryRowName & ", "
BuildString = strResult
End Function

You'll have to strip off the last trailing comma, otherwise you should be good to go. Good LucK!

 

[isorders]

I am not really sure how to use this. I am not much of a programmer.
The report displays results for a work order from tblWO.
In tblWONodes are the fields workorderno and nodeid and is joined to tblWO through WorkOrderNo.
The query (qryWONodes) is something like select * from tlbWONodes where WorkOrderNo = [Form!txtWorkOrderNo], so I only get the Nodes associated with the Work Order on the report. Also on that same report I want to display all of the NodeIDs from the query separated by commas.
So I would have WO 12345
with NodeIDs C44, F65, G55 etc...

 

[isorders]

I am almost there.
Here is what I did to get the results of my recordset into my form.

Function ListMH() As String
'Declare a variable to hold the comma separated values
Dim strCSVals As String
Dim fld As DAO.Field
Dim rst As DAO.Recordset

Set rst = CurrentDb.OpenRecordset("tblWONodes", dbOpenDynaset)
Set fld = rst("[NodeID]&quot

Do Until rst.EOF
strCSVals = strCSVals & fld & ", "

rst.MoveNext
Loop
Set rst = Nothing
'After the loop has ended you can trim up strVals like so:

strCSVals = Left(strCSVals, Len(strCSVals) - 2) 'This will remove the last character (space and comma) from the string
ListMH = strCSVals
End Function

Report txtListNodes Control Source = ListMH()

The problem is the tblWONodes I want to actually be a more select group of records from qryWONodes.

SELECT * FROM tblWONodes WHERE (((tblWONodes.WorkOrderNo)=[Forms]![frmWO]![WorkOrderNo]))ORDER BY tblWONodes.NodeID;

This works great if I just run the query. It only returns the results from the WorkOrderNo displayed on frmWO, but when I put it in CurrentDb.OpenRecordset("qryWONodes" I get a rst = Nothing Error.

Any ideas on what I am doing wrong. When I take the parameter out it works fine in the function.

 

[isorders]

Here is the final answer.

Function ListMH() As String
'Declare a variable to hold the comma separated values
Dim strCSVals As String
Dim fld As DAO.Field
Dim rst As DAO.Recordset
Dim WO As String
WO = Forms!frmWO!WorkOrderNo

Set rst = CurrentDb.OpenRecordset("SELECT WorkOrderNo, NodeID FROM tblWONodes WHERE WorkOrderNo= '" & WO & "';", dbOpenDynaset)
Set fld = rst("[NodeID]&quot

Do Until rst.EOF

strCSVals = strCSVals & fld & ", "

rst.MoveNext
Loop
Set rst = Nothing
'After the loop has ended you can trim up strVals like so:

strCSVals = Left(strCSVals, Len(strCSVals) - 2) 'This will remove the last character (comma) from the string
ListMH = strCSVals
End Function

 

[beetee]

I'm a little concerned that you are subtracting 2 from the length when you are removing only one comma

 

[isorders]

There is also a space in there.

 

[beetee]

Gotchya

 

[isorders]

I documented it in the final, but left it out in the comments on this post.