Mvf

[kparadise]

Hello,

I made the mistake of building to fields as Multi-Valued. They work find for my queries and tables, but now I am trying to put those fields onto a report. For easy of example. My MVF is a drop down on a form that allows you to select all the fruit you like: Apple, Orange, Blueberry, Banana, etc.
When i query the data, or even look into the table; it shows as the correct values separated by a comma; i.e. Orange, Banana.

That is what I want to show up on my report to.... Orange, Banana just like that, separated by a comma. Is this possible...reading up on MVF, i should have not done them, but it was the cleanest I could make my database. I am very new to Access.

 

[Micron]

Suggest you seriously consider 'fixing' the design decision. For a feature that has so many detractors, it seems odd that anyone would endorse more than a very limited use of it, yet you can find advocates. I think your problems will only compound should you need to use this table in additional ways, and from what I've read, there is no way to get this to work in a report as you wish. Apparently, you can repeat the values as record rows in a report, but that's not what you're asking for.

Be that as it may, you could look at how to manipulate the recordset for a multi value field, but it may prove to be more work than replacing the table you have. Basically, you have to create a recordset for the field, and a child recordset for the values of the field, looping through the child recordset. When you reach the end of the child recordset, you have to move to the next 'parent' record and do the inner loop again. While you are looping through the values, you'd have to append them to a string variable and set the control value to it. The big question is, how do you get this to show in a report? If you can't put a control in a group header or footer to pull this from a function because it might not work in the detail rows, then you might be faced with trying to get it to work in a report format event. That's not even something I'd want to venture in to. Here's a link on the subject of multi value recordsets:

https://msdn.microsoft.com/en-us/library/bb258183(v=office.12).aspx

 

[kparadise]

Dang; with my experience, it sounds like I should just have a subform in my normal form, that allows the user to select Banana, go to the next line, and select Orange.

Would this be a subform?

 

[Micron]

I thought you wanted to use the mv values in a report?
I'm only speculating due to the fact that I have never used a mv field (and never will), so I'll say that since these values seem to list as records in a query just fine, a data sheet or continuous form ought to behave the same way. However, your post expressed a desire to see the values represented as a separated list, not as values listed as separate records and I know of no way your'e going to get that without using the child recordset.

 

[kparadise]

No, I want to see them as they appear in the Datasheet View. "Banana, Orange"

However, when I pull in the Fruit.Value field into my query, and try to put that onto the report...it will create two records;
- Kyle | Banana
- Kyle | Orange

Instead of:
- Kyle | Banana, Orange

Is there a way to get the second option onto my report?

 

[Micron]

I already gave you the only solution I know of, and a link to it in my first post.

 

[kparadise]

Yea, that is way to complicated for me to understand. I might just add more fields, and label them "Fruit 1", "Fruit 2", "Fruit 3", etc....then have a field in me query to Concatenate them.

 

[kparadise]

Ha, I figured it out. All i had to do was right click-on the field in Report Design View and "CHANGE-TO" text box.