Concatrelatd () function (1 Viewer)

mansied

Member
Local time
Today, 08:47
Joined
Oct 15, 2020
Messages
99
Hello
I need help in concatenating a string field in rows :

1632194252264.png


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

I’m here to help
Staff member
Local time
Today, 05:47
Joined
Oct 29, 2018
Messages
21,357
Hi. It might be easier if you could post a sample db. Take a look at this simple function.

 

mansied

Member
Local time
Today, 08:47
Joined
Oct 15, 2020
Messages
99
ConcatRelated() function doesn't work in query.it is unknown
 

Gasman

Enthusiastic Amateur
Local time
Today, 12:47
Joined
Sep 21, 2011
Messages
14,039
ConcatRelated() function doesn't work in query.it is unknown
Really?
Code:
Expr1: ConcatRelated("CrewName","QryCrewStudy","QryCrewStudy.Rank = """ & [Rank]![Rank] & """ AND QryCrewStudy.On_Date = #" & Format([Dated],"mm/dd/yyyy") & "#","CrewNameRev")
1632209641298.png
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 08:47
Joined
May 21, 2018
Messages
8,463
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

Member
Local time
Today, 08:47
Joined
Oct 15, 2020
Messages
99
Really?
Code:
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
 

Attachments

  • Sample.accdb
    860 KB · Views: 88

theDBguy

I’m here to help
Staff member
Local time
Today, 05:47
Joined
Oct 29, 2018
Messages
21,357
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.

1632253049277.png


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

Enthusiastic Amateur
Local time
Today, 12:47
Joined
Sep 21, 2011
Messages
14,039
@theDBguy
The crewname code is mine, from my bibbys db, just to show the OP that the function does work in a query.?
 

mansied

Member
Local time
Today, 08:47
Joined
Oct 15, 2020
Messages
99
I added two tables to see what i expect to be the result .
Thank you
1632254789508.png
 

June7

AWF VIP
Local time
Today, 04:47
Joined
Mar 9, 2014
Messages
5,423
You did not even include the function code in db. Making it hard for us to help you.
 

mansied

Member
Local time
Today, 08:47
Joined
Oct 15, 2020
Messages
99
@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

Enthusiastic Amateur
Local time
Today, 12:47
Joined
Sep 21, 2011
Messages
14,039
Review the function notes? all the details are there?
Why not try theDBguy's version, if you are struggling with AB's
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 20:47
Joined
May 7, 2009
Messages
19,169
see Query1.
 

Attachments

  • Sample.accdb
    1.2 MB · Views: 90

Users who are viewing this thread

Top Bottom