Concatenating multiple fields and ignoring null fields
- Thread starter Invader89
- Start date
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.
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?
I see that we are communicating on two different threads so lets stick to this one so we don't get confused
.
Last edited:
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.
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:
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.
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.
