Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1076
  • Last Modified:

Access 2010 DConcat Function Order Field

I am using the DConcat function from
http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_2380-Domain-Aggregate-for-Concatenating-Values-by-Group-in-Microsoft-Access.html   

I have it working well.  However, I would like to change the order of the concatenated field.

the SQL query is:

SELECT tblNotes.SO_SUX_OP, tblNotes.ID_SO, tblNotes.SUFX_SO, tblNotes.ID_OPER, DConcat("note","tblNotes","[SO_SUX_OP] = '" & [SO_SUX_OP] & "'") AS Projects
FROM tblNotes
GROUP BY tblNotes.SO_SUX_OP, tblNotes.ID_SO, tblNotes.SUFX_SO, tblNotes.ID_OPER;


In my case, I am combining notes for a shop order.  The notes can concatenate ascending or descending.  I have a field named  SEQ_COMMENT that controls the order the notes.


SEQ_COMMENT             Note      
1                   This is the first note
2                   Note two
3                   Third note
4                       Note four
5                   Last Note

This is the output:
 Note four, Last last note, Note two, Third note, This is the first note

Trying to get:
This is the first note, Note two,  Third note,    Note four, Last last note


Is is possible to orderby on SEQ_COMMENT?

Thank you in advance.

The DConcat code is:

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)
   
    ' Function by Patrick G. Matthews, basically embellishing an approach seen in many
    ' incarnations over the years
   
    'http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_2380-Domain-Aggregate-for-Concatenating-Values-by-Group-in-Microsoft-Access.html
   
    ' Requires reference to Microsoft DAO library
   
    ' This function is intended as a "domain aggregate" that concatenates (and delimits) the
    ' various values rather than the more usual Count, Sum, Min, Max, etc.  For example:
    '
    '    Select Field1, DConcat("Field2", "SomeTable", "[Field1] = '" & Field1 & "'") AS List
    '    FROM SomeTable
    '    GROUP BY Field1
    '
    ' will return the distinct values of Field1, along with a concatenated list of all the
    ' distinct Field2 values associated with each Field1 value.
   
    ' ConcatColumns is a comma-delimited list of columns to be concatenated (typically just
    '   one column, but the function accommodates multiple).  Place field names in square
    '   brackets if they do not meet the customary rules for naming DB objects
    ' Tbl is the table/query the data are pulled from.  Place table name in square brackets
    '   if they do not meet the customary rules for naming DB objects
    ' Criteria (optional) are the criteria to be applied in the grouping.  Be sure to use And
    '   or Or as needed to build the right logic, and to encase text values in single quotes
    '   and dates in #
    ' Delimiter1 (optional) is the delimiter used in the concatenation (default is ", ").
    '   Delimiter1 is applied to each row in the code query's result set
    ' Delimiter2 (optional) is the delimiter used in concatenating each column in the result
    '   set if ConcatColumns specifies more than one column (default is ", ")
    ' Distinct (optional) determines whether the distinct values are concatenated (True,
    '   default), or whether all values are concatenated (and thus may get repeated)
    ' Sort (optional) indicates whether the concatenated string is sorted, and if so, if it is
    '   Asc or Desc.  Note that if ConcatColumns has >1 column and you use Desc, only the last
    '   column gets sorted
    ' Limit (optional) places a limit on how many items are placed into the concatenated string.
    '   The Limit argument works as a TOP N qualifier in the SELECT clause
   
    Dim rs As DAO.Recordset
    Dim SQL As String
    Dim ThisItem As String
    Dim FieldCounter As Long
   
    On Error GoTo ErrHandler
   
    ' Initialize to Null
   
    DConcat = Null
   
    ' Build up a query to grab the information needed for the concatenation
   
    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, "")
       
    ' Open the recordset and loop through it:
    ' 1) Concatenate each column in each row of the recordset
    ' 2) Concatenate the resulting concatenated rows in the function's return value
   
    Set rs = CurrentDb.OpenRecordset(SQL)
    With rs
        Do Until .EOF
           
            ' Initialize variable for this row
           
            ThisItem = ""
           
            ' Concatenate columns on this row
           
            For FieldCounter = 0 To rs.Fields.Count - 1
                ThisItem = ThisItem & Delimiter2 & Nz(rs.Fields(FieldCounter).Value, "")
            Next
           
            ' Trim leading delimiter
           
            ThisItem = Mid(ThisItem, Len(Delimiter2) + 1)
           
            ' Concatenate row result to function return value
           
            DConcat = Nz(DConcat, "") & Delimiter1 & ThisItem
            .MoveNext
        Loop
        .Close
    End With
   
    ' Trim leading delimiter
   
    If Not IsNull(DConcat) Then DConcat = Mid(DConcat, Len(Delimiter1) + 1)
   
    GoTo Cleanup

ErrHandler:
   
    ' Error is most likely an invalid database object name, or bad syntax in the Criteria
   
    DConcat = CVErr(Err.Number)
   
Cleanup:
    Set rs = Nothing
   
End Function
0
Scamquist
Asked:
Scamquist
  • 7
  • 4
  • 4
1 Solution
 
mbizupCommented:
Give this a try:


SELECT  t.SEQ_COMMENT, q.Projects, q.SO_SUX_OP, q.ID_SO, q.SUFX_SO, q.ID_OPER
FROM tblNotes t
INNER JOIN
(SELECT tblNotes.SO_SUX_OP, tblNotes.ID_SO, tblNotes.SUFX_SO, tblNotes.ID_OPER, DConcat("note","tblNotes","[SO_SUX_OP] = '" & [SO_SUX_OP] & "'") AS Projects
FROM tblNotes
GROUP BY tblNotes.SO_SUX_OP, tblNotes.ID_SO, tblNotes.SUFX_SO, tblNotes.ID_OPER) q
ON t.Notes = q.Notes
ORDER BY  t.SEQ_COMMENT

Open in new window

0
 
mbizupCommented:
Also try this:

SELECT tblNotes.SO_SUX_OP, tblNotes.ID_SO, tblNotes.SUFX_SO, tblNotes.ID_OPER, DConcat("note","tblNotes","[SO_SUX_OP] = '" & [SO_SUX_OP] & "'") AS Projects
FROM tblNotes
GROUP BY tblNotes.SO_SUX_OP, tblNotes.ID_SO, tblNotes.SUFX_SO, tblNotes.ID_OPER
ORDER BY tblNotes.SEQ_COMMENT

Open in new window

0
 
ScamquistAuthor Commented:
mbizup.  I tired both methods.

The first method:
I pasted the code into SQL screen.
error:
Microsoft Access can't represent the joni expression t.Notes=q.Notes in Design View.
If I try to view it asks to Enter Paramater Value for t.Notes and q.Notes



The second method gives error:
You tried to execute a query that does not include the specified expression 'tblNotes.SEQ_COMMENT" as part of an aggregate function
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
mbizupCommented:
Is Notes a text or memo field?  If it is a memo field, you can try this:


SELECT  t.SEQ_COMMENT, q.Projects, q.SO_SUX_OP, q.ID_SO, q.SUFX_SO, q.ID_OPER
FROM tblNotes t, 
(SELECT tblNotes.SO_SUX_OP, tblNotes.ID_SO, tblNotes.SUFX_SO, tblNotes.ID_OPER, DConcat("note","tblNotes","[SO_SUX_OP] = '" & [SO_SUX_OP] & "'") AS Projects
FROM tblNotes
GROUP BY tblNotes.SO_SUX_OP, tblNotes.ID_SO, tblNotes.SUFX_SO, tblNotes.ID_OPER) q
WHERE t.Notes = q.Notes
ORDER BY  t.SEQ_COMMENT

Open in new window

0
 
ScamquistAuthor Commented:
Notes is a 60 character text field.
0
 
mbizupCommented:
Ok - try my suggestion at http:#a39155823 just the same...

And don't switch to design view... just run it from SQL view.
0
 
Patrick MatthewsCommented:
Scamquist, please note that in the last comment on the article page, I offer a newer version of DConcat:

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 = "", _
    Optional Limit As Long = 0)
    
    ' Function by Patrick G. Matthews, basically embellishing an approach seen in many
    ' incarnations over the years
    
    ' Requires reference to Microsoft DAO library
    
    ' This function is intended as a "domain aggregate" that concatenates (and delimits) the
    ' various values rather than the more usual Count, Sum, Min, Max, etc.  For example:
    '
    '    Select Field1, DConcat("Field2", "SomeTable", "[Field1] = '" & Field1 & "'") AS List
    '    FROM SomeTable
    '    GROUP BY Field1
    '
    ' will return the distinct values of Field1, along with a concatenated list of all the
    ' distinct Field2 values associated with each Field1 value.
    
    ' ConcatColumns is a comma-delimited list of columns to be concatenated (typically just
    '   one column, but the function accommodates multiple).  Place field names in square
    '   brackets if they do not meet the customary rules for naming DB objects
    ' Tbl is the table/query the data are pulled from.  Place table name in square brackets
    '   if they do not meet the customary rules for naming DB objects
    ' Criteria (optional) are the criteria to be applied in the grouping.  Be sure to use And
    '   or Or as needed to build the right logic, and to encase text values in single quotes
    '   and dates in #
    ' Delimiter1 (optional) is the delimiter used in the concatenation (default is ", ").
    '   Delimiter1 is applied to each row in the code query's result set
    ' Delimiter2 (optional) is the delimiter used in concatenating each column in the result
    '   set if ConcatColumns specifies more than one column (default is ", ")
    ' Distinct (optional) determines whether the distinct values are concatenated (True,
    '   default), or whether all values are concatenated (and thus may get repeated)
    ' Sort (optional) indicates whether the concatenated string is sorted, and if so, the
    '   columns used for the sort.  As you would in an ORDER BY clause, use Asc or Desc to
    '   indicate whether the column is sorted ascending or descending.  If Asc/Desc is
    '   omitted, Asc is assumed by the query engine
    ' Limit (optional) places a limit on how many items are placed into the concatenated string.
    '   The Limit argument works as a TOP N qualifier in the SELECT clause
    
    Dim rs As DAO.Recordset
    Dim SQL As String
    Dim ThisItem As String
    Dim FieldCounter As Long
    
    On Error GoTo ErrHandler
    
    ' Initialize to Null
    
    DConcat = Null
    
    ' Build up a query to grab the information needed for the concatenation
    
    SQL = "SELECT " & IIf(Distinct, "DISTINCT ", "") & _
            IIf(Limit > 0, "TOP " & Limit & " ", "") & _
            ConcatColumns & " " & _
        "FROM " & Tbl & " " & _
        IIf(Criteria <> "", "WHERE " & Criteria & " ", "") & _
        IIf(Sort <> "", "ORDER BY " & Sort, "")
        
    ' Open the recordset and loop through it:
    ' 1) Concatenate each column in each row of the recordset
    ' 2) Concatenate the resulting concatenated rows in the function's return value
    
    Set rs = DBEngine(0)(0).OpenRecordset(SQL, dbOpenForwardOnly)
    With rs
        Do Until .EOF
            
            ' Initialize variable for this row
            
            ThisItem = ""
            
            ' Concatenate columns on this row
            
            For FieldCounter = 0 To rs.Fields.Count - 1
                ThisItem = ThisItem & Delimiter2 & Nz(rs.Fields(FieldCounter).Value, "")
            Next
            
            ' Trim leading delimiter
            
            ThisItem = Mid(ThisItem, Len(Delimiter2) + 1)
            
            ' Concatenate row result to function return value
            
            DConcat = Nz(DConcat, "") & Delimiter1 & ThisItem
            .MoveNext
        Loop
        .Close
    End With
    
    ' Trim leading delimiter
    
    If Not IsNull(DConcat) Then DConcat = Mid(DConcat, Len(Delimiter1) + 1)
    
    GoTo Cleanup

ErrHandler:
    
    ' Error is most likely an invalid database object name, or bad syntax in the Criteria
    
    DConcat = CVErr(Err.Number)
    
Cleanup:
    Set rs = Nothing
    
End Function

Open in new window


Implement that version of the UDF, and then modify your query to:

SELECT SO_SUX_OP, ID_SO, SUFX_SO, ID_OPER, 
    DConcat("note","tblNotes","[SO_SUX_OP] = '" & [SO_SUX_OP] & "'", ", ", ", ", True, "SEQ_COMMENT ASC") AS Projects
FROM tblNotes
GROUP BY SO_SUX_OP, ID_SO, SUFX_SO, ID_OPER;

Open in new window

0
 
ScamquistAuthor Commented:
mbizup.  suggestion at http:#a39155823

still returned t.Notes and q.Notes paramater requests
0
 
ScamquistAuthor Commented:
matthewspatrick.
Didn't notice the note.  I have used you Dconcate for ahwile now.  It is great.

The modified query returns #Error
0
 
Patrick MatthewsCommented:
Did you update both the VBA code and the query?

Can you post a (sanitized) copy of the database?
0
 
ScamquistAuthor Commented:
Yes, both VBA and Query.  I have attached a sample DB.
DConcat-Sample.accdb
0
 
Patrick MatthewsCommented:
OK, try this:

SELECT tblNotes.SO_SUX_OP, tblNotes.ID_SO, tblNotes.SUFX_SO, tblNotes.ID_OPER, DConcat("NOTE","tblNotes","[SO_SUX_OP] = '" & [SO_SUX_OP] & "'",",",",",False,"SEQ_COMMENT") AS Projects
FROM tblNotes
GROUP BY tblNotes.SO_SUX_OP, tblNotes.ID_SO, tblNotes.SUFX_SO, tblNotes.ID_OPER;

Open in new window


The problem is that in using Distinct = True, there was a SQL error: if you use SELECT DISTINCT, then if you use an ORDER BY clause it must include all columns in the SELECT clause.

Changing that argument to False avoided the error.
0
 
ScamquistAuthor Commented:
Perfect.  Thank you so much for your help.
0
 
ScamquistAuthor Commented:
OUTSTANDING!
0
 
Patrick MatthewsCommented:
Glad to help :)
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 7
  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now