• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 371
  • Last Modified:

function call

One of the experts gave me a function but i am having trouble calling it with a criteria option
this fails

the table has 10 tests for each code and i want a string representing tests that failed [testresult]=0  (testresult is a number field)

SELECT TblAbiTestResults.AbiCodeMvris, DConcatSC("[testdescription]","[TblAbiTestResults]","[testresult]=0") AS Expr1
FROM TblAbiTestResults;

this works (no criteria)
SELECT TblAbiTestResults.AbiCodeMvris, DConcatSC("[testdescription]") AS Expr1
FROM TblAbiTestResults;

am working in access query grid
Public Function DConcatSC(ConcatColumns As Variant, 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)

    ' 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 "
            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, "")
            ' Trim leading delimiter
            ThisItem = Mid(ThisItem, Len(Delimiter2) + 1)
            ' Concatenate row result to function return value
            DConcatSC = Nz(DConcatSC, "") & Delimiter1 & ThisItem
    End With
    ' Trim leading delimiter
    If Not IsNull(DConcatSC) Then DConcatSC = Mid(DConcatSC, Len(Delimiter1) + 1)
    GoTo Cleanup

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

Open in new window

  • 3
  • 2
1 Solution
I think the select statement should be

SELECT TblAbiTestResults.AbiCodeMvris, DConcatSC("[testdescription]","[TblAbiTestResults]","[testresult]","0") AS Expr1
‘FROM TblAbiTestResults;

Looks as if the Function requires a CriteriaField  and a CriteriaValue as separate values

Opps had a ' before the FROM
Use this

SELECT TblAbiTestResults.AbiCodeMvris, DConcatSC("[testdescription]","[TblAbiTestResults]","[testresult]=0") AS Expr1 FROM TblAbiTestResults;
PeterBaileyUkAuthor Commented:
neither work it says datatype mismatch in criteria expression

SELECT TblAbiTestResults.AbiCodeMvris, DConcatSC("[testdescription]","[TblAbiTestResults]","[testresult]=0") AS Expr1
FROM TblAbiTestResults;

this is as i had tried
PeterBaileyUkAuthor Commented:
ok i will rephrase the question. I just tried creating a query that has already filtered on testresult 0 BUT although the function worked it returned ten rows ie a row for each test.

I have attached a spredsheet with results for one code

for the attached I would like a string returned "Transmission, CC"
Re datatype mismatch
Maybe take the quotes off the 0 so it comes in as a real variable
It looks as if the function replaces single quotes but not double quotes in the line
realCriteriaValue = "='" & Replace(CriteriaValue, "'", "''") & "' "

Try this
SELECT TblAbiTestResults.AbiCodeMvris, DConcatSC("[testdescription]","[TblAbiTestResults]","[testresult]",0) AS Expr1 FROM TblAbiTestResults;


Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now