ConcatRelated (1 Viewer)

Workaholic_711 · Apr 9, 2019

Hello experts,

I am learning VBA coding and came across the following issue:

I have an access database which has a table named CPT_CODES_String. It has the following fields:
CLAIM_ID, SERVICE_DATE, CPT_CODES, KEY(CLAIM_ID+SERVICE_DATE)

One Claim can have mutiple cpt codes. I want to concatenate CPT Codes corresponding to a Claim_ID in a single row.

For example:
Claim ID CPT Code
1 A
1 B
1 C
2 Z
2 E

Desired Output:
Claim ID CPT Code
1 A,B,C
2 Z,E

I tried Allen Browne's ConcatRelated but failed to write the vba code and also the ConcatRelated function using fields from one table only.

Could you please help me write the function as well as the vba code using the above table and field names?

Thank you.

theDBguy · Apr 9, 2019

Hi. Not sure why you would need to create a function. You're supposed to just use the one AllenBrowne provided. Here's another example of the same thing: SimpleCSV()

pbaldy · Apr 9, 2019

What did you try that failed? The query would look like:

SELECT [Claim ID], ConcatRelated(...)
FROM Tablename
GROUP BY [Claim ID]

With the appropriate arguments filled in for Allen's function.

Workaholic_711 · Apr 9, 2019

I tried the folowing select statement:
SELECT DISTINCT [CLAIM_ID], ConcatRelated([PROCEDURE_CODES],[003_Claim_CPT_Codes],"[CLAIM_ID] = """ & [CLAIM_ID])
FROM 003_Claim_CPT_Codes group by [CLAIM_ID]

pbaldy · Apr 9, 2019

And what happend? The quotes in Allen's examples are required, so more like:

ConcatRelated("[PROCEDURE_CODES]", "[003_Claim_CPT_Codes]", "[CLAIM_ID]= " & [CLAIM_ID])

Workaholic_711 · Apr 9, 2019

I forgot to add the quotes. It's running but taking a long time...Will let you know the outcome as soon as it finishes running.

Thank you!

Workaholic_711 · Apr 10, 2019

It's not working correctly. i got the following output:

CLAIM_ID Expr1001
ABC CPT1[CLAIM_ID] = "ABCCPT2[CLAIM_ID] = "ABCCPT3[CLAIM_ID] = "ABCCPT4[CLAIM_ID] = "ABCCPT5[CLAIM_ID] = "ABCCPT6
XYZ CPT1[CLAIM_ID] = "XYZCPT2[CLAIM_ID] = "XYZCPT3[CLAIM_ID] = "XYZCPT4[CLAIM_ID] = "XYZCPT5[CLAIM_ID] = "XYZCPT6

This is my select statement:
SELECT DISTINCT [CLAIM_ID], ConcatRelated("[PROCEDURE_CODES]","[CptTest]","[CLAIM_ID] = " & [CLAIM_ID])
FROM [CptTest] group by [CLAIM_ID]

pbaldy · Apr 10, 2019

Is Claim ID text or numeric (its data type)? If text:

ConcatRelated("[PROCEDURE_CODES]","[CptTest]","[CLAIM_ID] = '" & [CLAIM_ID] & "'")

Can you attach the db here?

Workaholic_711 · Apr 10, 2019

IT WORKED!!!!!

THANK YOU SO MUCH!!! CLAIM_ID was a text field.

pbaldy · Apr 10, 2019

Happy to help! Allen's site addressed the field being text.

Workaholic_711 · Apr 10, 2019

Is there a way i can set the field to hold more than 255 characters. The output is truncated where the string is more than 255 characters. I tried setting the final output field as Long text but that didnt seem to make any dent.

Thanks again for your help!

pbaldy · Apr 11, 2019

Sorry, I've never come close to that so haven't needed it.

theDBguy · Apr 11, 2019

Workaholic_711 said:
Is there a way i can set the field to hold more than 255 characters. The output is truncated where the string is more than 255 characters. I tried setting the final output field as Long text but that didnt seem to make any dent.

Thanks again for your help!

Hi. Can you post your final query SQL? Thanks.

Workaholic_711 · Apr 11, 2019

Here you go:

SELECT [Raw Data].Key, Trim(ConcatRelated("[Value]","[Raw Data]","[Key] = '" & [Key] & "'")) AS FinalString INTO [Final Report]
FROM [Raw Data]
GROUP BY [Raw Data].Key;

Workaholic_711 · Apr 11, 2019

I ran into a new issue with this: My team wants a list box with multiple delimiter options. I created a list box and tried to plug the listbox.ItemsSelected in the strSeperator field, but it's not working.

pbaldy · Apr 11, 2019

This is a shot in the dark, but it could be that the make table won't create a long text field. Try creating a table with that specified, and make this an append query instead of a make table query.

How did you try to pass the listbox value to the function?

Workaholic_711 · Apr 11, 2019

Thanks, I 'll change it to an append query.

so this is what i did:

Public Function ConcatRelated(strField As String, _
strTable As String, _
Optional strWhere As String, _
Optional strOrderBy As String, _
Optional strMyListbx As String, _
Optional strMyListbox = lstDelimit.ItemsSelected, _
Optional strSeperator = strMyListbox) As Variant

It gave me a compile error

pbaldy · Apr 11, 2019

No, you wouldn't change the function, you'd pass the listbox value to it. Something like

ConcatRelated("[Value]","[Raw Data]","[Key] = '" & [Key] & "'", , Me.ListboxName)

I suppose you could hard-code it into the function, but I'd just pass it.

Workaholic_711 · Apr 12, 2019

i have it like this but it's erroing out:

INSERT INTO [Final Report] ( [Key], FinalString )
SELECT [Raw Data].Key, ConcatRelated("[Value]","[Raw Data]","[Key] = '" & [Key] & "'",[Forms]![frmStringDB].[lstDelimit]) AS FinalString
FROM [Raw Data]
GROUP BY [Raw Data].Key;

pbaldy · Apr 12, 2019

What's the error? Can you attach the db here?

ConcatRelated (1 Viewer)

Registered User.

I’m here to help

Wino Moderator

Registered User.

Wino Moderator

Registered User.

Registered User.

Wino Moderator

Registered User.

Wino Moderator

Registered User.

Wino Moderator

I’m here to help

Registered User.

Registered User.

Wino Moderator

Registered User.

Wino Moderator

Registered User.

Wino Moderator

Similar threads

Users who are viewing this thread