DConcat not working - getting #Error in query results

Karen Schaefer
Karen Schaefer used Ask the Experts™
on
I had the code working then I went to make a modification to the query using the Dconcat function and now I am getting #Error.  

Here is my current query - what am I missing?
Note the query works great if I remove the DConcat portion of the query - so what is wrong with my syntax?


SELECT Qry_PrgUODetail.WS_NO_UO AS WS_NO, DConcat("[MN]","[Qry_PrgUODetail]","[WS_NO_UO] = '" & [WS_NO_UO] & "'") AS MNList, Max(IIf([APNo]="ZB001","X",'')) AS ZB001, Max(IIf([APNo]="ZB002","X",'')) AS ZB002, Max(IIf([APNo]="ZB021","X",'')) AS ZB021
FROM Qry_PrgUODetail
GROUP BY Qry_PrgUODetail.WS_NO_UO;
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Expert of the Quarter 2010
Expert of the Year 2010

Commented:
Hi,

DConcat is not a built-in Access query function.
Did you copy the reference from a web site?  It looks like a Function defined in a Module, can you list the code so we know what it expects?
Top Expert 2010

Commented:
What data type is WS_NO_UO?  If it is numeric, try:SELECT Qry_PrgUODetail.WS_NO_UO AS WS_NO, DConcat("[MN]","[Qry_PrgUODetail]","[WS_NO_UO] = " & [WS_NO_UO]) AS MNList, Max(IIf([APNo]="ZB001","X",'')) AS ZB001, Max(IIf([APNo]="ZB002","X",'')) AS ZB002, Max(IIf([APNo]="ZB021","X",'')) AS ZB021FROM Qry_PrgUODetailGROUP BY Qry_PrgUODetail.WS_NO_UO;
Karen SchaeferBI ANALYST

Author

Commented:
it is alphanumberic(text) and I did try changing the quotes to single quotes. - Tried your change and now the null values are returning the #ERROR.

K
Karen SchaeferBI ANALYST

Author

Commented:
Sorry misspoke - All records are returning the #error.

k
Expert of the Quarter 2010
Expert of the Year 2010

Commented:
Do you actually have DConcat in a module?
What are its input params?
Which version did you copy from the web if you did not write it yourself?
Top Expert 2010

Commented:
Top Expert 2010

Commented:
MDB format, please :)
Karen SchaeferBI ANALYST

Author

Commented:
It will take a while to create a dummy - can't send copy of my current database - it has propriatry data and is linked to multiple datasources.

k
Top Expert 2010

Commented:
Yes, do be careful not to send up sensitive information :)
Karen SchaeferBI ANALYST

Author

Commented:
Here is my sample mdb
DConcatTest.mdb
Expert of the Quarter 2010
Expert of the Year 2010

Commented:
Fixed by CK
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
    
    ' 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
'MsgBox SQL
    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

Open in new window

Expert of the Quarter 2010
Expert of the Year 2010

Commented:
Oops.... haven't fixed the DConcat function, but here's a fixed query for you
Change your DConcat expression to this

DConcat("[MN]","[Qry_PrgUODetail]","[WS_NO_UO] " & IIF(WS_NO_UO is null, " IS NULL", "= '" & Replace([WS_NO_UO],"'","''") & "'")) AS MNList


Patrick's DConcat doesn't handle nulls or quotes in the filter.
Karen SchaeferBI ANALYST

Author

Commented:
cyberkiwi:
Thanks for the input however I am still getting the same results #Error

K
Expert of the Quarter 2010
Expert of the Year 2010

Commented:
DConcatSC on a single column, with correct usage.
Derived from Patrick's code.

You can use this with your original code like this

SELECT Qry_PrgUODetail.WS_NO_UO AS WS_NO, DConcatSC("[MN]","[Qry_PrgUODetail]","[WS_NO_UO]", [WS_NO_UO]) AS MNList, Max(IIf([APNo]="ZB001","X",'')) AS ZB001, Max(IIf([APNo]="ZB002","X",'')) AS ZB002, Max(IIf([APNo]="ZB021","X",'')) AS ZB021
FROM Qry_PrgUODetail
GROUP BY Qry_PrgUODetail.WS_NO_UO;
Function DConcatSC(ConcatColumns As String, Tbl As String, Optional CriteriaField As String = "", _
    Optional CriteriaValue As Variant, _
    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
    
    ' 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, DConcatSC("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
    
    DConcatSC = Null
    
    ' Build up a query to grab the information needed for the concatenation
    
    Dim realCriteriaValue As String
    If CriteriaField <> "" Then
        If IsNull(CriteriaValue) Then
            realCriteriaValue = " IS NULL "
        Else
            realCriteriaValue = "='" & Replace(CriteriaValue, "'", "''") & "' "
        End If
    End If
    SQL = "SELECT " & IIf(Distinct, "DISTINCT ", "") & _
            IIf(Limit > 0, "TOP " & Limit & " ", "") & _
            ConcatColumns & " " & _
        "FROM " & Tbl & " " & _
        IIf(CriteriaField <> "", "WHERE " & CriteriaField & realCriteriaValue, "") & _
        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
            
            DConcatSC = Nz(DConcatSC, "") & Delimiter1 & ThisItem
            .MoveNext
        Loop
        .Close
    End With
    
    ' Trim leading delimiter
    
    If Not IsNull(DConcatSC) Then DConcatSC = Mid(DConcatSC, Len(Delimiter1) + 1)
    
    GoTo Cleanup

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

Open in new window

Expert of the Quarter 2010
Expert of the Year 2010

Commented:
Hi,

>Thanks for the input however I am still getting the same results #Error
If this is against http:#a33148993, please see http:#a33149004 on how to change your query.

Otherwise, you can now use the approach in http:#a33149076
Top Expert 2010
Commented:
Karen,Why didn't you tell me that you changed the source code?Your problem was introduced by a line you added.  If you had mentioned that you changed the source code, we all could have saved a lot of time :)1) Remove this line:If Left(DConcat, 2) = ", " Then DConcat = LTrim(DConcat) - 22) Rewrite your query as:SELECT tblTEst.WS_NO_UO, DConcat("[MN]","[tblTEst]","[WS_NO_UO]='" & [WS_NO_UO] & "' And Nz([MN], """") <> """"") AS MNList, Max(IIf([APNo]="ZB001","X",'')) AS ZB001, Max(IIf([APNo]="ZB002","X",'')) AS ZB002, Max(IIf([APNo]="ZB021","X",'')) AS ZB021FROM tblTEstGROUP BY tblTEst.WS_NO_UO;
Top Expert 2010

Commented:
CK,I was absolutely wracking my brain trying to figure out what was going wrong, then I saw a weird line, thinking "why the heck would I have done that?!?"Of course, the answer was, I didn't do it :)Patrick
Top Expert 2010

Commented:
Karen,In my revision above, I am assuming that you want to suppress any Null or zero length string results for MN.  Was that a correct assumption?Patrick
Karen SchaeferBI ANALYST

Author

Commented:
Thanks Patrick, sorry for the confusion, I was just trying to handle the comma for the null values.

That fixed the problem.

Karen
Top Expert 2010

Commented:
No worries, Karen.  Next time, though, please mention stuff like that, because it will save us both a lot of time.Cheers,Patrick
Expert of the Quarter 2010
Expert of the Year 2010
Commented:
Patrick,

Regardless, DConcat still doesn't work in 2 cases:

DConcat("[MN]","[Qry_PrgUODetail]","[WS_NO_UO]  = '" & [WS_NO_UO] & "'")

(1) when [ws_no_uo] is NULL.  Your SQL being built throws a "Invalid use of null"
(2) when [ws_no_uo] contains a single quote.

This is the "usage hint" in the function
    '    Select Field1, DConcat("Field2", "SomeTable", "[Field1] = '" & Field1 & "'") AS List
    '    FROM SomeTable
    '    GROUP BY Field1
Well, it doesn't work when Field1 contains single quotes.

Thoughts?

CK
Karen SchaeferBI ANALYST

Author

Commented:
Thanks guys for the assist
Top Expert 2010

Commented:
CK,Testing now.Patrick
Top Expert 2010

Commented:
CK,If you go back to my original source code, it seems to be handling a Null in WS_NO_UO.You are correct that a singlequote in WS_NO_UO will break it.I could try escaping it in the code, but that could be a tricky proposition, because I would want to leave alone singlequotes in a bracketed column name.Patrick
Expert of the Quarter 2010
Expert of the Year 2010

Commented:
Patrick,

My bad. I was playing with it and broke the null condition.  Didn't click that Access does str&null => str instead of Sql Server's str + null => null.

Would it be prudent to change the usage hint to this?

    '    Select Field1, DConcat("Field2", "SomeTable", "[Field1] = '" & Replace(Field1,"'","''") & "'") AS List
    '    FROM SomeTable
    '    GROUP BY Field1

Without testing, I think Access can handle quotes around anything, even numbers, so that it is safe to quote anything.
I know Sql Server can.
Top Expert 2010

Commented:
CK,I tried adding some records with Null in WS_NO_UO, and with values having singlequotes.A Null does not throw an error, but it does return a Null for DConcat, essentially because any time you test Null = Null you always get False.  My inclination is to do nothing about that, because generally speaking, how often does one actually group on a null?The singlequote thing does throw an error, and if you have any suggestions as to how I could overcome that, I would appreciate it.Patrick
Top Expert 2010

Commented:
OK, I was typing and testing while you were composing that :)Based on your suggestion, this query is successfully handling the singlequote:SELECT tblTEst.WS_NO_UO, DConcat("[MN]","[tblTEst]","[WS_NO_UO]='" & Replace(Nz([WS_NO_UO],""),"'","''") & "' And Nz([MN], """") <> """"") AS MNList, Max(IIf([APNo]="ZB001","X",'')) AS ZB001, Max(IIf([APNo]="ZB002","X",'')) AS ZB002, Max(IIf([APNo]="ZB021","X",'')) AS ZB021FROM tblTEstGROUP BY tblTEst.WS_NO_UO;Note the extra Nz() in the Replace() expression; I needed that so that running a Replace against a Null did not throw an error :)I will go ahead and post a comment in the article thread mentioning that criteria fields with singlequotes in the values require special handling.Cheers,Patrick
Expert of the Quarter 2010
Expert of the Year 2010

Commented:
This one way up above is simple enough and handles NULL and replace in one go.

DConcat("[MN]","[Qry_PrgUODetail]","[WS_NO_UO] " & IIF(WS_NO_UO is null, " IS NULL", "= '" & Replace([WS_NO_UO],"'","''") & "'")) AS MNList
Expert of the Quarter 2010
Expert of the Year 2010

Commented:
>> how often does one actually group on a null?
I wouldn't go about doing it intentionally, but a simple

GROUP BY Category

where Category is a nullable field will inevitable get into the situation.

DConcatSC (above) is such simpler to understand for the majority case that simply wants to filter on one condition (the single column group by), but my bias is obvious.
Top Expert 2010

Commented:
Well, I think there is room in this world for more than one flavor of DConcat :)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial