Concatrelatd () function

[mansied]

Hello
I need help in concatenating a string field in rows :

I want to do group by AC Model and Ac Serial,then concatenate all AC event text strings for it together.
Can you provide me a solution ?
Thank you

 

[theDBguy]

Hi. It might be easier if you could post a sample db. Take a look at this simple function.

Code Snippets

 

[arnelgp]

see also Allen Browne's ConcatRelated function:
Microsoft Access tips: Concatenate values from related records (allenbrowne.com)

 

[mansied]

ConcatRelated() function doesn't work in query.it is unknown

 

[Gasman]

ConcatRelated() function doesn't work in query.it is unknown

Really?

Expr1: ConcatRelated("CrewName","QryCrewStudy","QryCrewStudy.Rank = """ & [Rank]![Rank] & """ AND QryCrewStudy.On_Date = #" & Format([Dated],"mm/dd/yyyy") & "#","CrewNameRev")

 

[MajP]

ConcatRelated() function doesn't work in query.it is unknown

It is not a built-in access SQL function, but you need to make a user defined function.
See @arnelgp link to get a useable function

 

[mansied]

Really?

Expr1: ConcatRelated("CrewName","QryCrewStudy","QryCrewStudy.Rank = """ & [Rank]![Rank] & """ AND QryCrewStudy.On_Date = #" & Format([Dated],"mm/dd/yyyy") & "#","CrewNameRev")

View attachment 94630

Why it doesn't work for me ?
I want to do group by Aircraft Type,Aircraft Manufacturer Serial Number,and Event Type fields then concatenate Aircraft Event Text (Corrective Action) field.
Can you help how to do that ?
Thank you

 

[theDBguy]

Why it doesn't work for me ?
I want to do group by Aircraft Type,Aircraft Manufacturer Serial Number,and Event Type fields then concatenate Aircraft Event Text (Corrective Action) field.
Can you help how to do that ?
Thank you

Hi. Your expression appears to be trying to concatenate the "CrewName" column from a query called "QryCrewStudy." Looking at the file you posted, I don't see that query, nor do I see a column called CrewName in your table.

Can you please use Excel to mockup some sample data? One side shows the original data, as they exist in your table, and the other side showing what you want as the result of combining them together. Thanks.

 

[Gasman]

@theDBguy
The crewname code is mine, from my bibbys db, just to show the OP that the function does work in a query.?

 

[mansied]

I added two tables to see what i expect to be the result .
Thank you

 

[June7]

You did not even include the function code in db. Making it hard for us to help you.

 

[mansied]

@theDBguy
The crewname code is mine, from my bibbys db, just to show the OP that the function does work in a query.?

@theDBguy
The crewname code is mine, from my bibbys db, just to show the OP that the function does work in a query.?

Expr1: ConcatRelated("CrewName","QryCrewStudy","QryCrewStudy.Rank = """ & [Rank]![Rank] & """ AND QryCrewStudy.On_Date = #" & Format([Dated],"mm/dd/yyyy") & "#","CrewNameRev")

Could you express this code ? which one is the field were grouped by , what is table or query name and which field was concatenated???

 

[Gasman]

Review the function notes? all the details are there?
Why not try theDBguy's version, if you are struggling with AB's

 

[theDBguy]

I added two tables to see what i expect to be the result .
Thank you
View attachment 94658

Hi. Not sure if this is what you want; but hopefully, it will give you some idea.

 

[mansied]

Thank you
It does not work for me what is simplecsv ?

 

[theDBguy]

Thank you
It does not work for me what is simplecsv ?
View attachment 94662

If you imported the query from the file I posted, then make sure you import the Module "modDBguy" as well.

 

[arnelgp]

see Query1.

 

[mansied]

I really

see Query1.

I really appreciate that .my problem with this code solved .Thank you