Microsoft Access Form To Query for Multi Value Field

[KevinMS]

I am trying to write an Microsoft Access query from a form with a multi value field being the criteria. The field I am trying to use is called Population and the field is represented in my database as a List Box that allows multiple values and it is in the Building table. The values it allows are "Singles";"Familes";"Families with Children";"Youth/Young Adults";"Veterans";. The form that I am creating is called HousingSearch.

I am trying to create a form which uses this field, so someone could use the list box and check off the values they want and click on the button which would open a query. I know having done this with single value fields the criteria in the query looks something like



[forms]![HousingSearch]![Building]![Population]

but if you try the same thing for a multi value field nothing is returned for the query. Any help would be appreciated.

[June7]

Are you saving the actual descriptive text or an ID value for the population types?

Review: http://office.microsoft.com/en-us/ac...297.aspx#BM4.6

If you want to search for multiple values in a single field, will require code that builds a criteria string from items selected in UNBOUND listbox. Review: http://www.allenbrowne.com/ser-50.html

[KevinMS]

I am saving the actual text value.

[June7]

From what I understand of multi-value fields, will have to build query that expands the multi-value field to individual records (first link) then use code to build the filter string (second link) as criteria to filter the expanded query.

Bing: filter multi-value field
Another reference http://www.access-programmers.co.uk/...d.php?t=199778

I NEVER use multi-value fields.

[KevinMS]

I don't believe I will use a multi-value field again and I am going to change this database to use junction tables instead. Seems like this isn't worth the headache. Thank you for your help

[June7]

That will make the true structure of the data transparent but won't eliminate the issue of multiple parameters on one field.