PeterBaileyUk
asked on
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.AbiCodeM vris, DConcatSC("[testdescriptio n]","[TblA biTestResu lts]","[te stresult]= 0") AS Expr1
FROM TblAbiTestResults;
this works (no criteria)
SELECT TblAbiTestResults.AbiCodeM vris, DConcatSC("[testdescriptio n]") AS Expr1
FROM TblAbiTestResults;
am working in access query grid
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.AbiCodeM
FROM TblAbiTestResults;
this works (no criteria)
SELECT TblAbiTestResults.AbiCodeM
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 "
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
Opps had a ' before the FROM
Use this
SELECT TblAbiTestResults.AbiCodeM vris, DConcatSC("[testdescriptio n]","[TblA biTestResu lts]","[te stresult]= 0") AS Expr1 FROM TblAbiTestResults;
Use this
SELECT TblAbiTestResults.AbiCodeM
ASKER
neither work it says datatype mismatch in criteria expression
SELECT TblAbiTestResults.AbiCodeM vris, DConcatSC("[testdescriptio n]","[TblA biTestResu lts]","[te stresult]= 0") AS Expr1
FROM TblAbiTestResults;
this is as i had tried
SELECT TblAbiTestResults.AbiCodeM
FROM TblAbiTestResults;
this is as i had tried
ASKER
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"
eeex.xlsx
I have attached a spredsheet with results for one code
for the attached I would like a string returned "Transmission, CC"
eeex.xlsx
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SELECT TblAbiTestResults.AbiCodeM
‘FROM TblAbiTestResults;
Looks as if the Function requires a CriteriaField and a CriteriaValue as separate values
Don