Tricky Syntax issue with ConcatRelated function (1 Viewer)

:banghead:

To put this in context, I've been struggling with this issue for 3 days, and finally joined the forum to ask this question:

I'm still on the steep part of the learning curve when it comes to VBA code. I tracked down Allen Browne's ConcatRelated function, and installed it in a module, per the instructions. I got as far as it returning ALL values from the lookup field, but cannot seem to get the syntax right to limit the output correctly. Here is a sample of the source data, which is a query:

ls_id resp_party convert3
10004 LESSOR Other
10004-2013-0446 LESSOR Other
10005-1992-1189 LESSOR Grounds
10005-1992-1189 LESSOR Janitorial
10005-1992-1189 LESSOR Other
10005-1992-1189 LESSOR Public Utilities
10007-1992-1201 LESSOR Grounds
10007-1992-1201 LESSOR Janitorial
10007-1992-1201 LESSOR Other


And here is what I would like my query to return:

ls_id resp_party convert3
10004 LESSOR Other
10004-2013-0446 LESSOR Other
10005-1992-1189 LESSOR Grounds, Janitorial, Other, Public Utilities
10007-1992-1201 LESSOR Grounds, Janitorial, Other

Here is the query I built to achieve this, which is obviously incorrect:

SELECT RespTable.ls_id, ConcatRelated("[RespTable]![convert3]","RespTable","[ls_id] =" & [ls_id]) AS Responsibilites
FROM RespTable
GROUP BY RespTable.ls_id;

It currently gives error 3464 about data type mismatch. I have removed the third variable altogether and had it return ALL values from the convert3 column for each ls_id. I have tried numerous combinations of []'s, ""'s, """"", and spaces / & symbols according to different threads I've found, to no avail.

The ls_id field, as you can see, has dashes (non-alpha) characters, and is formatted as text.

Any help, will, of course be greatly appreciated. Thanks in advance.

Hi. Not exactly what you're asking for but just in case it might be easier for you, please consider trying out this simple function.

Here's the ConcatRelated documentation:

http://www.allenbrowne.com/func-concat.html

I see two issues with this:


You are referencing the field with the table name ([RespTable]![convert3]). The second argument is the table, so no need for it in the first argument. Second, [ls_id] is text and you are not handling it as such in the 3rd argument. You must surround it with single quotes.

Give this a shot:

Nailed it. Thank you. I was also able to add the fourth argument to Order it by the Convert3 field so they always appear in alpha order.

The apostrophe placement was the key difference between my closest attempt and your answer.

Works perfectly now.

Garook said:

Works perfectly now.

Click to expand...

Hi. Congratulations! Glad to hear you got it sorted out.