SteveL13
asked on
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?
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?
ASKER
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"
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
The function takes several parameters, not just one. You need the column AND the table. the others are optional.
ASKER
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","tblActivi tyBackOrde rs")
=DConcat("Item","tblActivi
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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