- Local time
- Today, 18:33
- Joined
- Feb 19, 2002
- Messages
- 47,936
The ONLY good thing about MVF's is the "cool" control with the checkboxes which of course with a small amount of effort, you can model using a subform.
How to Properly Query and Search Multi-Valued Fields in a Form? (2 Viewers)
- Thread starter Anurag R
- Start date
Thank you for taking your time and providing me the alternate solution. The solution provided my @arnelgp is exactly what I was looking for and it meets the requirement that was given to me without any issues. However, I really appreciate your help on this matter and I will consider it for my future work.
Ken Sheridan
Active member
- Local time
- Today, 23:33
- Joined
- Jul 10, 2025
- Messages
- 112
Nevertheless, I would still advise that, as a beginner now is the time for you to get out of bad habits, and eschew the use of multi valued fields for the reasons given by myself and other respondents in this thread.
In relational database terms what you are doing is an example of Relational Division. There are established SQL solutions for this. The solutions in the little demo file I posted earlier are really variations of the established methodologies, in my case referencing a multi-select list box.
MajP
You've got your good things, and you've got mine.
- Local time
- Today, 18:33
- Joined
- May 21, 2018
- Messages
- 9,825
@arnelgp example is probably the hardest thing to do and has a lot going on. This demonstrates using an MVF control with a helper table to build a filter for an MVF field. Here is another example that is does the same thing but has several MVF controls to filter several MVF fields (not my example).
However as I mentioned you can filter an MVF without a MVF control. May make it a little easier to understand then you can work your way up to filtering MVFs with MVFs. These variations are not as complicated. You can use a single select combobox to return any MVF fields that contain just that one value. Or slightly more complicated you can use a multselect listbox to filter a multi value field. This is a little clearer than using an MVF to filter the MVF.
Maybe what is most useful is using an MVF to filter a normal field. I demo these three since @arnelgp and the example I posted already demo using the MVF to filter the MVF.
1. Example 1 uses just a standard combobox to filter the MVFs. The only thing to remember is that you do not filter the MVF field but the .Value of the MVF field. This is not a .value like a control but actually a reference to a field in the hidden junction table. You can see the filter string build in the textbox.
2. This example uses a standard multiselect listbox to filter a multivalue field.
What is confusing about these multi value fields is that it is a "lookup" and displays the activity, devices, sensors, system names but all these MVF store the related key. I discuss this here in detail
Again notice the filter includes .value after the MVF field name
3. This demo may be most useful. Many people like the MVF control, but not the MVF field. This demos how you can use the MVF control to filter a standard field.
I do not use MVFs much but I disagree with most people's arguments. If you understand how they work, as I tried to list you are unlikely to have any issues. There are limitations, but most people simply want to store a list of values.
A lot of experienced Access developers are often wrong in their arguments or they focus on the 5% possible problem.
The following arguments are stated all the time and are wrong
1. "They store multiple data elements in a field"
2. "The are not normalized data"
3. "You cannot enforce referential integrity or unique values"\
4. "They are difficult to query or limited in what can be queried"
The following arguments are legit, but they are not common issues for most users wanting an MVF and mostly non problems.
1. "You cannot migrate to SQl Server." But if there is a chance you will migrate then you probably will know that ahead of time. Even if you do, I guarantee converting the MVF field to a junction or child table is the least of the difficulties to overcome. Unless you have hundreds of MVFs like some recent poster. Using a simple query you can build a junction table from the MVF very quickly.
2. Kens points about binary relationships and non key attributes is also correct, but also unlikely to be an issue when wanting to use an MVF. If you know you need other attributes or other tables in your relationship, pretty sure you will not ponder an MVF.
The real issue with MVFs is that they obfuscate what is going on. It is two layers of confusion. The real data is normal and correct, but resides in a hidden table that you cannot see or touch. On top of that the field uses a lookup. So the hidden values which could be foreign keys are often not displayed, but the related text description is. Lookup columns are confusing on themselves, but now they are displaying values from a hidden table.
MS maybe could have done a favor and not called them MVFs but MV Displays (MVD). They are not a field but a user interface. In fact it is a column and a table and special control properties. The data for the field is in the hidden table and the thing called the MVF is just a display. Or at least they could show the hidden table, but make it not editable.
Faking an MVF to give its same capabilities is doable, but not easy.
1. Small real estate (multi select drop down). Unlike a subform or listbox
2. Show concatenated solutions
3. Save data in a normal child/junction table
But is is not easy. I have several versions, but they are all pretty involved and very hard to replicate making a common and reusable solution.
However as I mentioned you can filter an MVF without a MVF control. May make it a little easier to understand then you can work your way up to filtering MVFs with MVFs. These variations are not as complicated. You can use a single select combobox to return any MVF fields that contain just that one value. Or slightly more complicated you can use a multselect listbox to filter a multi value field. This is a little clearer than using an MVF to filter the MVF.
Maybe what is most useful is using an MVF to filter a normal field. I demo these three since @arnelgp and the example I posted already demo using the MVF to filter the MVF.
1. Example 1 uses just a standard combobox to filter the MVFs. The only thing to remember is that you do not filter the MVF field but the .Value of the MVF field. This is not a .value like a control but actually a reference to a field in the hidden junction table. You can see the filter string build in the textbox.
2. This example uses a standard multiselect listbox to filter a multivalue field.
What is confusing about these multi value fields is that it is a "lookup" and displays the activity, devices, sensors, system names but all these MVF store the related key. I discuss this here in detail
MVF Technical Discussion and Uses (If you plan to Use Them)
Since there is a lot of discussion now on MVF, I created this thread to provide discussion on understanding how they work, what you can do with them, and how to use them. There is plenty of discussion of the cons of MVF and what you cannot do with them, so no need to rehash that here. However...
www.access-programmers.co.uk
3. This demo may be most useful. Many people like the MVF control, but not the MVF field. This demos how you can use the MVF control to filter a standard field.
I do not use MVFs much but I disagree with most people's arguments. If you understand how they work, as I tried to list you are unlikely to have any issues. There are limitations, but most people simply want to store a list of values.
A lot of experienced Access developers are often wrong in their arguments or they focus on the 5% possible problem.
The following arguments are stated all the time and are wrong
1. "They store multiple data elements in a field"
2. "The are not normalized data"
3. "You cannot enforce referential integrity or unique values"\
4. "They are difficult to query or limited in what can be queried"
The following arguments are legit, but they are not common issues for most users wanting an MVF and mostly non problems.
1. "You cannot migrate to SQl Server." But if there is a chance you will migrate then you probably will know that ahead of time. Even if you do, I guarantee converting the MVF field to a junction or child table is the least of the difficulties to overcome. Unless you have hundreds of MVFs like some recent poster. Using a simple query you can build a junction table from the MVF very quickly.
2. Kens points about binary relationships and non key attributes is also correct, but also unlikely to be an issue when wanting to use an MVF. If you know you need other attributes or other tables in your relationship, pretty sure you will not ponder an MVF.
The real issue with MVFs is that they obfuscate what is going on. It is two layers of confusion. The real data is normal and correct, but resides in a hidden table that you cannot see or touch. On top of that the field uses a lookup. So the hidden values which could be foreign keys are often not displayed, but the related text description is. Lookup columns are confusing on themselves, but now they are displaying values from a hidden table.
MS maybe could have done a favor and not called them MVFs but MV Displays (MVD). They are not a field but a user interface. In fact it is a column and a table and special control properties. The data for the field is in the hidden table and the thing called the MVF is just a display. Or at least they could show the hidden table, but make it not editable.
Faking an MVF to give its same capabilities is doable, but not easy.
1. Small real estate (multi select drop down). Unlike a subform or listbox
2. Show concatenated solutions
3. Save data in a normal child/junction table
But is is not easy. I have several versions, but they are all pretty involved and very hard to replicate making a common and reusable solution.
Similar threads
