Store Multiple Values in a Single Textbox in Microsoft Access

Question

I've searched a lot of forums but can't find the exact answer I need.

My db is a Conference Scheduling DB whereby I have four tables to consider: Persons / Person-to-Conference / Conference_Main / Conference_Day

I am unable to post images because when I try to do so on my work computer, it craps out, so I'm sorry for the lack of visual.

This db will allow someone to create a conference, where it will automatically populate data for Day 1. Not all conferences are multiple days, but some are.

Let's say there's a three-day conference (Conference X), and it's attended by three people (A, B, C).

Person A is going Day 1
Person B is going Day 1 & 3
Person C is going Day 2 & 3

I have a query where I want to show what days of the conference people attended and I want to populate a textbox on a form with that information. Is there a simple concatenation I can do, or do I need a For Each loop, because of the question of whether or not a conference will even have multiple days or not?

I want to see something like this:

Attendee   Days Attended
Person A   1
Person B   1, 3
Person C   2, 3

*Note "Days Attended" would be the textbox in question where I'd want to see the multiple values, e.g. "2, 3"

I'd rather not store this in another table, but rather just make it Dynamic based on whatever days I know my person is going to for the conference. Can anyone help me with some vba?

Or should this instead be a Query that will concatenate the Days attended? Is that possible?

My answer has nothing to do with it being entered in correctly (it is), but rather is a question of where I should be calling the variable arguments for the WHERE statement.

This sounds like a good use for a `Listbox` instead, honestly. — braX, Nov 08 '17 at 17:41
Cross posted http://www.accessforums.net/showthread.php?t=69024 — June7, Nov 08 '17 at 19:37
Possible duplicate of [ConcatRelated function in a query](https://stackoverflow.com/questions/18940038/concatrelated-function-in-a-query) — June7, Nov 08 '17 at 19:52
I don't get how this is a "possible duplicate". Can you please explain how you got to that conclusion? — Will Dutcher, Nov 09 '17 at 14:31
you used the term "query" and you want to concatenate the result of the query — jsotola, Nov 10 '17 at 16:37
Someone helped me with my WHERE statement from that AllenBrowne.comblink. It needed to look something like this: — Will Dutcher, Nov 11 '17 at 18:25

score 1 · Answer 1 · answered Nov 09 '17 at 16:24

1

I received a ton of help here: AccessForums which made reference to this post on AllenBrowne.com. Thank you to all that helped!

answered Nov 09 '17 at 16:24

Will Dutcher

339
1
4
11

score 1 · Answer 2 · answered Nov 11 '17 at 18:36

Someone helped me with my WHERE statement from that AllenBrowne.comblink. It needed to look something like this:

I needed to add this to the control source of the textbox: =ConcatRelated("Day", "qry_PerToConfByDay", "PersonID=" & [PersonID] & " AND Conference ID=" & [ConferenceID]

Whereby:

Start of Function is: ConcatRelated(

Field I want to see all values is: "Day",

Table (or in this case the Query) I want to pull the data from: "qry_PerToConfByDay",

Where criteria, setting up which fields I want to pull from: "PersonID=" & [PersonID] & " AND ConferenceID=" & [ConferenceID]

End the function: )

I didn't need to do an ORDER BY nor did I need to change my delimiter, so the last two arguments I didn't put in. But that's what worked for me.

I hope this is helpful to someone else!

Store Multiple Values in a Single Textbox in Microsoft Access

2 Answers2