Access 2016 multi-value fields

Question

I'm trying to use a query to concatenate several fields together to create a sentence...

One of the fields I need to use is a multi-value field...

If I put just that field in my query it returns the list of all of the items in the field.

As soon as i try to concatenate anything else on it I get the error about not being able to use a multi-value field...

I don't really care how I accomplish this... I only went with query since I have already written a bunch of them to make sentences

with regular fields and it worked great.

Additional Info...

[Fname] - short text

[Lname] - short text

[ItemsBrought] - Multi-value field (it's an allow multiple selections combo box on my form)

fname - John

lname - Sharp

itemsbrought - chair, table, multi-tool

sql

select [Fname] & " " & [Lname] & " brought these items with him..." & [ItemsBrought]

from mytable

(The above SQL works fine up until I put the [itemsbrought] field into the query)

my intended result is...

John Sharp brought these items with him...chair, table, multi-tool

Answer 1

I've posted an amended copy of your file to as JohnSharp.accdb to my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

You table did not actually include a multi-valued field, so rather than using that, I've modelled the many-to-many relationship type between notes and skills in the conventional way by means of a table which resolves the relationship type into two one-to-many relationship types.  Take a look at the relationships window to  see the model.

In your skills table you'd also use multiple columns for the skill names, where you should have used a single column with multiple rows.  I've corrected this structure.

For data entry I've amended your form by including a subform within the parent notes form.  Multiple skills per note can thus be entered by inserting multiple rows in the subform.  Note how a new skill, not currently repre3sentedin the combo box's drop down list, can be entered into the skills table by typing the new name into the combo box.  Code in the combo box's NotInList event procedure then, subject to user confirmation, inserts a new row into the skills table.

To concatenate the skills into a comma separated list I've added a function, which you'll find in the basGetSkills module.  This is a function specific to this task, rather than generic one like that Daniel referred you to earlier, or that in the Con cat.zip file in my OneDrive folder.  It is similar to that which I posted in my earlier reply, but in this case for use with a conventionally modelled many-to-many relationship type, rather than one modelled by a multi-valued field.

I've included a query which calls the function, and a simple report based upon the query.

Answer 2

From looking at your solution I can see where I went wrong...

I think with a small redesign of my tables I can make it work...

I thank you very much for getting me pointed in the right direction...

I just couldn't see the forest because of all the trees.

John