Mutli-valued fields in reports

[austinboy_1998]

Hi -

I am using a few multivalued fields in my database and am having some issues generating reports using them. I have seen tons of documentation on how to resolve a query using a multivalued field but not much on how to use them in reports. I have several reports I am trying to build that show columns for each of the mutli-valued options. For example:

US Asia EMEA
Project 1 X X
Project 2 X X

I was going to use iif statements for each of the columns. So if a multivalued field for region contained a value, show an "X". I tried different options and could not figure out how to determine if the Multivalued field included a specific value. i.e if region included US as one of the multiple values. This iif approach worked if there was only one value selected (US), but if there were multiple values, the "=" doesn't work. Does anyone know the correct way to determine if a multivalue field contains a specific value as one of the selections? Or is there a better way to build this report?

Thanks in advance,
B.

[austinboy_1998]

I made a bit of progress that provided another clue but I am still struggling. I created a text box for the report column value with an iif statement for the control source that said iif([regions.value] = "US", "X", "NA"). (i was going to do similar for EMEA column and Latam column) This IIF statement for US caused multiple entries to show up in the report, one for each of the multiple values. In the example I used previously, Project 1 region(s) field which had values of "US", "EMEA", "Asia" showed up as:

Project name US EMEA LATAM
Project 1 X
Project 1 NA
Project 1 NA
Project 2 X

When I removed the .value from the end of the regions field in the IIF statement, only one line showed up. In this scenario, if only US was selected, this evaluated to true and the X showed up where it was supposed to - worked fine. If US and EMEA were both selected as values, this did not evaluate to true in the IIF statement. Only project 2 showed up but project 1 should have too:

Project name US EMEA LATAM
Project 1 NA
Project 2 X

I feel like the Iif statement is comparing "US" to "US", "EMEA", "LATAM" and evaluating false. I can't figure out how to determine if "US" is contained in the "Regions" list for a specific record.

After reading posts for several hours, I see that these multivalued fields are a bad idea in general unless you are tying to sharepoint. That is exactly what I am doing tying to Sharepoint, that is why I took this approach.

I have about 4 similar reports to build like this so I would GREATLY appreciate any insight anyone could give. Am I barking up the wrong tree with my report approach or is there a simple way to fix this?

Thanks again,
Brandon