Show entire list of multivalue field in report

[dlackey74]

I know that multivalue fields are not the best to use, but I have a simple database that they work fine in. My question is this: Is there a way to show the entire list of choices in the multivalue field on a report? Not just the ones selected, but all of them, whether checked or not? The multivalue field has a list of names and the user selects each name if they repond to a incident (fire, car accident, etc.). My report would show the entire list and count number of responses by each name during a date range. I want to show all of them on the report, with those not selected showing "0". Any ideas?

 

[boblarson]

The query that you use in the Report must select the field explicitly (not using SELECT *) and you need to add .VALUE at the end

Select Field1, Field2, Field3, MVFField.Value, Field5 From TableNameHere

 

[dlackey74]

Thanks for the reply.

Here is my SQL:

SELECT [Event Report].[Members Involved].Value
FROM [Event Report]
WHERE ((([Event Report].EventDate) Between [Forms]![Date Range Response]![txtStartDate] And [Forms]![Date Range Response]![txtEndDate]));

This only shows the items in the list that were checked. I want to show all items in list whether checked or not.

The report shows total repsponses from each member during the chosen date range, layout like this on report:

Members Responses
Member1 1
Member6 4
Member9 2


I want it to show like this:

Members Responses
Member1 1
member2 0
Member3 0
Member4 0
Member5 0
Member6 4

and so forth.

Any ideas would be very appreciative!

 

[boblarson]

Thanks for the reply.

Here is my SQL:

SELECT [Event Report].[Members Involved].Value
FROM [Event Report]
WHERE ((([Event Report].EventDate) Between [Forms]![Date Range Response]![txtStartDate] And [Forms]![Date Range Response]![txtEndDate]));

This only shows the items in the list that were checked.

Yes, that is what it would get to start with.

I want to show all items in list whether checked or not.

For that you would need to link the table which gives the lookup its values (I assume you do have a table, if it is a value list then you are going to need to create a table with the values).

You will take the first query and create a new query with the lookup table joined with an outer join from the lookup table to the first query's [Members Involved].Value field. You then have

MembersInvolved:IIf(Len([Members Involved].Value & "")=0, 0, [Members Involved].Value)