How to take all data from a column in query, and show in one form field (1 Viewer)

MushroomKing

Registered User.
Local time
Today, 01:28
Joined
Jun 7, 2018
Messages
100
Hi everyone!

I know you're "not supposed to do this" within Access according to some other forums. But i need to do this because the information needs to be fed this way to another application.

My question:

How can i take an entire column from my query, and show the data in my form field?

Ofcourse i manage to do this with 1 record, but i want all the data from the column. Please help :) Thanks!
 

MushroomKing

Registered User.
Local time
Today, 01:28
Joined
Jun 7, 2018
Messages
100
Thanks for the swift reply CJ!

Works like a charm :)
 

MushroomKing

Registered User.
Local time
Today, 01:28
Joined
Jun 7, 2018
Messages
100
But not quiet LOL

So it DOES concat all the numbers from the column.
But as you can see in my SQL, i have a WHERE clause where i'm saying that i want all the numbers in the column where ordernumber is NULL.

Right now it gives me only the record where ordernumber is NULL,
But in my expression are ALL the numbers and it ignores the WHERE part....

Whats going on :S

Code:
SELECT staging_t.container_ID, ConcatRelated("container_ID","staging_t") AS Expr1
FROM staging_t
WHERE (((staging_t.ordernumber) Is Null));
 

essaytee

Need a good one-liner.
Local time
Today, 19:28
Joined
Oct 20, 2008
Messages
512
But not quiet LOL

So it DOES concat all the numbers from the column.
But as you can see in my SQL, i have a WHERE clause where i'm saying that i want all the numbers in the column where ordernumber is NULL.

Right now it gives me only the record where ordernumber is NULL,
But in my expression are ALL the numbers and it ignores the WHERE part....

Whats going on :S

Code:
SELECT staging_t.container_ID, ConcatRelated("container_ID","staging_t") AS Expr1
FROM staging_t
WHERE (((staging_t.ordernumber) Is Null));

Have you tried including the Where clause in the function ConcatRelated and not at the end of the SQL clause. As per the example at Allan Browne's site.
Code:
SELECT CompanyName,  ConcatRelated("OrderDate", "tblOrders", "CompanyID = " & [CompanyID]) 
FROM tblCompany;
 

MushroomKing

Registered User.
Local time
Today, 01:28
Joined
Jun 7, 2018
Messages
100
Hi essaytee! Thanks!

No i havent. But i did read this part...

Any valid WHERE clause is permitted.
If you omit this argument, ALL related records will be returned.

You're right...he probably was talking about the function itself :).

But i have no idea how to include it into the function.
On top of that theyre pressuring me for time over here :eek:

I would also assume, my query FIRSTLY filters my data, and THEN it would use the function since i put the fields in that order...but i guess not....
I'll try something :(
 

CJ_London

Super Moderator
Staff member
Local time
Today, 08:28
Joined
Feb 19, 2013
Messages
16,553
But i have no idea how to include it into the function.
essay has provided a solution. and it is on Allens site. And as I said - like the DLookup function. If this is not what you want, provide some example data from your Staging_t table and what you want returned. And you did say it was to appear in a form control, so not sure why you are using it in a query
 

MushroomKing

Registered User.
Local time
Today, 01:28
Joined
Jun 7, 2018
Messages
100
Hi CJ,

But the solution essay put up there is a quote of the SQL code...
I dont understand how to use my where clause or where to put it.

Yes, i would like to use it in my form field...but i only get syntax errors.
I'm still learning and studying everything. But now i needed to ask for help because im so crammed for time.

Code:
=ConcatRelated("ordernumber", "staging_t") WHERE ("ordernumber" Is Null));

I got one more hour to fix this and i dont know what the hell im doing LOL
 

CJ_London

Super Moderator
Staff member
Local time
Today, 08:28
Joined
Feb 19, 2013
Messages
16,553
the thing about examples is they are for a particular scenario - which may not be your scenario.

You have not fully explained what you are trying to achieve and not provided example data as requested so makes it difficult to suggest a solution which works for you.

the query example would be used in a recordsource to a form. the function on its own would be used as a control controlsource

this

=ConcatRelated("ordernumber", "staging_t") WHERE ("ordernumber" Is Null));

should be this (just like Dlookup as mentioned many times)

=ConcatRelated("ordernumber", "staging_t","ordernumber Is Null")

however the code does not make sense - you are concatenating null values
 

essaytee

Need a good one-liner.
Local time
Today, 19:28
Joined
Oct 20, 2008
Messages
512
=ConcatRelated("ordernumber", "staging_t","ordernumber Is Null")

however the code does not make sense - you are concatenating null values

CJ, you more or less mentioned what I was going to mention. I too was struggling with the logic. Maybe there is logic there but not clearly explained.

M..King: Slightly off-topic, I'm curious about the deadline, that you only have one hour to solve it. I know I say this from afar, don't stress. What will happen if you don't resolve it within the hour? Nothing of consequence (I hope).
 

Grumm

Registered User.
Local time
Today, 09:28
Joined
Oct 9, 2015
Messages
395
Maybe this does the trick :

Code:
SELECT staging_t.container_ID, ConcatRelated("container_ID","staging_t","staging_t.ordernumber Is Null") AS Expr1
FROM staging_t
WHERE (((staging_t.ordernumber) Is Null));

That will concat all the container_ID from the staging table where ordernumber is null.
 
Last edited:

MushroomKing

Registered User.
Local time
Today, 01:28
Joined
Jun 7, 2018
Messages
100
Thanks you so much guys. As always, great community here and not comparable to other forums on any coding language.

Code:
=ConcatRelated("pallet_ID", "staging_t","ordernumber Is Null")

Is what i used on my form field. Works like a charm.
I was stressing too much :)
 

Users who are viewing this thread

Top Bottom