0

I have an Access application that I am building to create some reporting. I have all of my lists linked however I have a hiccup when it comes to the people fields. All of the people picker fields are set to multiselect.

Looking at the linked table data with a query it looks like:

 123; 124

This makes sense so I joined to the UserInfo table and I thought I would get:

 Last, First; Last, First

Instead I got two records. For reporting, two records would be bad because it would artificially inflate any counts or sums. When you export to Excel straight from the SharePoint list you get the data the way I'd need it.

The question I have is how do I get my query to display as one record with the names instead of the UserInfo IDs?

You cannot use a make table query because Access tells you that a multi-valued field cannot be part of a SELECT INTO statement.

I tried to create a custom function whereby if I find a ";" then I need split the names and look them up individually. I get the same basic error. The multi-valued field is not a valid argument. I tried setting the parameter as a string, a variant and not defined and I get the same thing every time.

  • 1
    Do it after the fact? perhaps this can help: stackoverflow.com/questions/18640815/… – Aboba Apr 22 '14 at 17:33
  • The problem with that is my source table is the SharePoint list and it is already formatted that way. However if I used a make table query on my link table then I could use the ADO to be able to do this. – AxGryndr Apr 22 '14 at 18:30
2

I used this as a guide - see the section USING AN APPEND QUERY WITH A MULTIVALUED FIELD https://support.office.com/en-US/article/Using-multivalued-fields-in-queries-6F64F92D-659F-411C-9503-B6624E1E323A#bm8

  • This is some good information about how the multiselect field works that didn't used to be there. Thanks. – AxGryndr Jun 21 '16 at 19:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.