Cannot get ConcatRelated() Function to work

[yosko1]

Using Allen Browne's ConcatRelated()Function

Have tbl that looks like:

Prgm    Level No    Target
3801    300104      983
3801    300104      106
3801    300104      1026
3801    550201      1027
3801    550201      127
B4X     200407      1518
B4X     200407      2898
B4X     200407      2299
B4X     200407      22
ABC     150401      339

Wanting to get these results:

Prgm    Level No    PossibleTrgts
3801    300104      983, 106, 1026
3801    550201      1027, 127
B4X     200407      1518, 2898, 2299, 22
ABC     150401      339

Using this query:

SELECT DISTINCT Tbl.[Prgm], Tbl.[Level No], ConcatRelated([Target],"Tbl","[Prgm]=""" & [Prgm] & """" And [Level No]=""" & [Level No] & """") AS PossibleTrgts
FROM Tbl;

Using MS Access 2010 32bit
Prgm and Level No fields are text
Target field is number


Getting error message: Syntax error in string in query expression 'ConcatRelated([Target],"Tbl","[Prgm]=""" & [Prgm] & """" And [Level No]=""" & [Level No] & """") AS PossibleTrgts
FROM Tbl;'.


Would appreciate any help in this matter using 2 fields.

[Seth Schrock]

Try the following

SELECT DISTINCT Tbl.[Prgm]
, Tbl.[Level No]
, ConcatRelated("[Target]","Tbl","[Prgm]='" & [Prgm] & "' And [Level No]='" & [Level No] & "'") AS PossibleTrgts
FROM Tbl;

[Rabbit]

I'm pretty sure Target needs to be in quotes as well.

[Seth Schrock]

@ Rabbit You are correct about that. I missed that originally.

[yosko1]

@Seth Schrock
Still get syntax error msg. After clicking on OK button the query is highlited in black from: "') AS PossibleTrgts
FROM Tbl;

This still after adding quotes to "[Target]"

[Seth Schrock]

I have added another missing double quote right before the ending parenthesis. Try my code from post #2 again.

[yosko1]

Thank you Seth and Rabbit for your help. Got compile error but once I fixed that, it's working now. Thank you again for your quick response and consideration in this matter.