Tom,
I have been shovelling and skyping.
I have looked at removing the duplicates, now that there is an option to sort output from ConcatRelated.
I spent some time proving it shouldn't be done inside ConcatRelated. Then I said, why not just process the output with a user defined function.
So here is the function:
Code:
' ----------------------------------------------------------------
' Procedure Name: removeDuplicates
' Purpose: Remove duplicate value from a separator based- Ordered string
' Procedure Kind: Function
' Procedure Access: Public
' Parameter StrList (String): List of values to be parsed to remove duplicate values
' Return Type: String
' Author: Jack
' Date: 17-Jan-22
'
'INFO: This was set up to remove duplcates from an Ordered result from ConCatRelated.
'NOTE: The incoming string should already be ordered, but may have duplicates
' ----------------------------------------------------------------
Function removeDuplicates(StrList As String) As String
10 On Error GoTo removeDuplicates_Error
Dim good As String 'this is the string to be returned
Dim var As Variant 'variable to contain an array after split of strLIst
Dim el As Variant ' an iterator for reading individual elements of arraay
Dim unkn As Variant 'a starting value for comparison with el
Dim strSep As String 'separator for output ", "
strSep = ", "
20 unkn = "Z"
' StrList = "1 ,1 ,1,2 ,2,3 ,4 ,4 ,6" 'test setup
30 StrList = Replace(StrList, " ", "")
40 var = Split(StrList, ",")
50 For Each el In var
60 If el <> unkn Then
70 good = good & el & strSep 'uses ", " separator; this could be an optional char
80 unkn = el
90 Else
'skip a duplicate
100 End If
110 Next
removeDuplicates = Left(good, Len(good) - Len(strSep))
120 On Error GoTo 0
removeDuplicates_Exit:
130 Exit Function
removeDuplicates_Error:
140 MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure removeDuplicates, line " & Erl & "."
150 GoTo removeDuplicates_Exit
End Function
And here is how you call it.
SELECT Query1.foundword
,
removeDuplicates(ConcatRelated("[Jobtitleid]","[01_tbl_WordDictionary_NoDups]","[foundword] ='" & [foundword] & "'","jobtitleId")
) AS ConCaTEst
FROM Query1
WHERE (((Query1.foundword) Not Like "*'*")
And here is partial result.