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

Sorry for the late reply.  I was out of town over the weekend.  I did just download your file and I am currently looking things over. 

Thanks again

Answer 3

Have you downloaded the amended copy of your file from my OneDrive folder?  If I do not hear from you within the next 48 hours the file will be removed.

Answer 4

Ken,

     I thought I had a pretty firm handle on relational databases... but I just can't seem to figure this out...

here is a link..

https://1drv.ms/f/s!AgPzs-na_cpZxULYRLYOsHdFFVPc

This should link to a sample database in my onedrive.  I created the problem with only the tables and fields I am dealing with on this issue.  I just can't seem to figure out the function.

If you are able to look at my test database...  you will see one report in it and I am trying to concatenate a field + some text + my multivalue field in a query and then display that query result on my form.

Thanks again

John

Answer 5

Ken,

   Thank you for the additional explanation.  I will give this a go and let you know the results. 

Thanks again!

John