Combine field values from more then record into a single query field

I'm trying to figure out how to combine field values into one query field.  

So if for example the records in a table look like:

Publication, Jan/2015
Publication, Feb/2015

I want the field in the query to show "Publication, Jan/2015, Publication, Feb/2015"  (Note the comma separator)

But also I need to include a criteria where "ActivityID" equals a number.

So the expression field in the query might look something like"

Items: =[CombinedFieldValues]  

How can I do this?
SteveL13Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

 
PatHartmanCommented:
Here is an article that contains a function that will concatenate values.  I haven't used it so I can't vouch for it but it looks good.

A suggestion though - when you do the concatenation, do not use a coma as the separator since the field already includes multiple values separated by a coma.  If anyone downstream ever has to parse the string, they won't be able to do it based on the delimiter.
ConcatFunctionExample.docx
0
 
SteveL13Author Commented:
I guess I don't get it.  I pasted the following copy into a module, (with the commented lines removed to make it shorter here).  Plus in a query designer I entered this code --  

Items:  DConcat("Item")

But I get an error on the query field:  "The expression you entered has a function containing the wrong number of arguments"

Function DConcat(ConcatColumns As String, Tbl As String, Optional Criteria As String = "", _
    Optional Delimiter1 As String = ", ", Optional Delimiter2 As String = ", ", _
    Optional Distinct As Boolean = True, Optional Sort As String = "Asc", _
    Optional Limit As Long = 0)
    
    Dim rs As DAO.Recordset
    Dim SQL As String
    Dim ThisItem As String
    Dim FieldCounter As Long
    
    On Error GoTo ErrHandler
    
    DConcat = Null
    
    SQL = "SELECT " & IIf(Distinct, "DISTINCT ", "") & _
            IIf(Limit > 0, "TOP " & Limit & " ", "") & _
            ConcatColumns & " " & _
        "FROM " & Tbl & " " & _
        IIf(Criteria <> "", "WHERE " & Criteria & " ", "") & _
        Switch(Sort = "Asc", "ORDER BY " & ConcatColumns & " Asc", _
            Sort = "Desc", "ORDER BY " & ConcatColumns & " Desc", True, "")
    
    Set rs = CurrentDb.OpenRecordset(SQL)
    With rs
        Do Until .EOF
           
            ThisItem = ""
            
            For FieldCounter = 0 To rs.Fields.Count - 1
                ThisItem = ThisItem & Delimiter2 & Nz(rs.Fields(FieldCounter).Value, "")
            Next
            
            ThisItem = Mid(ThisItem, Len(Delimiter2) + 1)
            
            DConcat = Nz(DConcat, "") & Delimiter1 & ThisItem
            .MoveNext
        Loop
        .Close
    End With
    
    If Not IsNull(DConcat) Then DConcat = Mid(DConcat, Len(Delimiter1) + 1)
    
    GoTo Cleanup

ErrHandler:
    
    DConcat = CVErr(Err.Number)
    
Cleanup:
    Set rs = Nothing
    
End Function

Open in new window

0
 
PatHartmanCommented:
The function takes several parameters, not just one.  You need the column AND the table.  the others are optional.
0
 
SteveL13Author Commented:
Now I'm getting all the items not just the ones related to "ActivityID".  I have this in the form field:  (I gave up on putting it in a query field)

=DConcat("Item","tblActivityBackOrders")
0
 
PatHartmanCommented:
Please examine ALL the arguments for the function.  You will see that one of them allows you to specify Criteria.
0

Experts Exchange Solution brought to you by ConnectWise

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.