Concatenating multiple fields and ignoring null fields (1 Viewer)

Invader89

Registered User.
Local time
Today, 07:50
Joined
Sep 22, 2015
Messages
29
Hello,

I need some help in concatenating multiple fields while ignoring fields that have no values in them. The concatenation also needs to separate the values with a comma.
 
Last edited:

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 19:50
Joined
May 7, 2009
Messages
19,169
Names: (Column1 + ",") & (Column2 + ",") & (Column3 + ",") & (Column4 + ",") & LastColumn
 

sneuberg

AWF VIP
Local time
Today, 04:50
Joined
Oct 17, 2014
Messages
3,506
arnelgp's solution seems to work fine and certainly is simpler than mine. I didn't think you could concatenate nulls like that. In any case I as I said in the other thread these fields look like they should be data in another table.
 

Invader89

Registered User.
Local time
Today, 07:50
Joined
Sep 22, 2015
Messages
29
Names: (Column1 + ",") & (Column2 + ",") & (Column3 + ",") & (Column4 + ",") & LastColumn

Thanks, this method seems to work. However I see that after it concatenates the programs that a comma is left at the end. Any way to fix this?
 

sneuberg

AWF VIP
Local time
Today, 04:50
Joined
Oct 17, 2014
Messages
3,506
If you decide to go with the two table structure I suggested, let me know and I'll send you code that will produce this string from those tables without the coma at the end.
 

Invader89

Registered User.
Local time
Today, 07:50
Joined
Sep 22, 2015
Messages
29
If you decide to go with the two table structure I suggested, let me know and I'll send you code that will produce this string from those tables without the coma at the end.


I see that we are communicating on two different threads so lets stick to this one so we don't get confused :).
 
Last edited:

sneuberg

AWF VIP
Local time
Today, 04:50
Joined
Oct 17, 2014
Messages
3,506
The query All Personnel in the attached database produces what you want for all of the records in the Personel table, I think. I wrote a function to produce the string of names. I can't think of any other way of doing that. If the database ends up with lot of records this is going to be a slow query.

Please take a look at the report I put in this. It's messy because I did it in a hurry, but it's how this is normally done. If you could live with the programs being in columns this would be the way to go. I doesn't require any code.

I didn't include anything to select the people. I wanted to get this back to you. If you need help on that let me know.
 
Last edited:

Invader89

Registered User.
Local time
Today, 07:50
Joined
Sep 22, 2015
Messages
29
Thanks sneuberg! the All Personnel.
 
Last edited:

sneuberg

AWF VIP
Local time
Today, 04:50
Joined
Oct 17, 2014
Messages
3,506
After the user selects multiple programs let say in a list box and clicks on a button, can the output you are looking for appear in a report or does it have to appear in a subform?

I cannot find a solution to this that involves a subform. https://support.microsoft.com/en-us/kb/135546 shows a way of making a filter with multiple selections but that results in duplicates in the result as one person could be multiple programs. However there might be some way to do this in a report. At least it might be easier to get rid of the duplicates.
 

Users who are viewing this thread

Top Bottom