472,959 Members | 1,678 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,959 software developers and data experts.

How to Count Multi Value Field

I have a multi value field selected from a dropdown list which can have many value like A 01, B 90, C 40, etc.
How to count this. Please help me as I am newbie in MS Access.
Jun 1 '21 #1
5 3388
32,547 Expert Mod 16PB
Exactly what is it that you want to count?

I believe that MVFs come as standard with a Field that indicates how many items there are in the MVF. On the other hand you may be after the number of items in your ComboBox Control. That also has a .ListCount property that tells you how many items are in its list.
Jun 1 '21 #2
443 Expert Mod 256MB
Just for info, attachment fields (which are of course a special type of MVF) do have a built in file counter but 'standard' MVFs don't have the equivalent feature.

If you want to count the number of MVF items in each record create an aggregate query grouping by the primary key field and count the MVF field. For example
Expand|Select|Wrap|Line Numbers
  1. SELECT tblMVF.ID, Count(tblMVF.MVF_Field) AS CountOfMVF_Field
  2. FROM tblMVF
  3. GROUP BY tblMVF.ID;
Jun 2 '21 #3
32,547 Expert Mod 16PB
Just for info, attachment fields (which are of course a special type of MVF) do have a built in file counter but 'standard' MVFs don't have the equivalent feature.
Ah. That makes sense. I avoid them myself if I'm honest so not the best source of such info.
Jun 3 '21 #4
443 Expert Mod 256MB
Yes, I avoid them as well. In fact I wrote this article Multivalued fields... and why you really shouldn't use them
Jun 3 '21 #5
32,547 Expert Mod 16PB
Colin Riddington - Mendip Data Systems:
Whilst it is possible to run action queries involving MVFs it is very messy.
It is very easy to get extremely confused about the data and make mistakes doing so.
By contrast, if you had stored your data in a normalised table with one value per record, searching, filtering & editing would be trivial.
It would also only require ONE table rather than THREE
Nice one. Very well presented and I love your conclusion statements. It makes it easy to understand why they really aren't a good idea to get involved with.
Jun 3 '21 #6

Sign in to post your reply or Sign up for a free account.

Similar topics

by: pmarisole | last post by:
I am trying to use the vbscript "split" function on a multi-select field. I am trying to do a mass update of several records at a time. I am getting an error and I'm not sure what to do. Here is...
by: stoogots2 | last post by:
I have a field named sList in a Sql Server database table. The field has multiple delimited values (delimited with "|"). Example could be "1|2|3|4|5". I have a view with several fields (including...
by: lupis | last post by:
I am pretty new to vba. This is what I am trying to achieve. I have a form that onload displays all records in a form. I also have a searchbox with a button that should be able to take multiple...
by: amy1 | last post by:
Hello everyone, I'm new here and new to Access2007 as well! I have a multi-value field in a form, and would like to calculate the total of the selected values in this field and place the...
by: timleonard | last post by:
I use a query to select several fields from the table including a multi-value field. I then use the "DoCmd.TransferSpreadsheet acExport acSpreadsheetTypeExcel12" to send the info to an excel.xlsm...
by: jaad | last post by:
how do you reference a single value field to a multi-value field? I sometime use a macro in form1 to open form 2 containing the same ID example: Open form: WorkOrder where condition: ="="...
by: WaqasAnsari | last post by:
This is what I am working on. When patients count is 0, it works, but when it is increased, suppose 1, it gives an error: "An UPDATE or DELETE query doesn't contain Multi Value Field." What's the...
by: Michael R | last post by:
Hi. I'm using Access 2010 x86 I'm trying to create a Multi Value Field in a table using DAO, with the following code: Sub Test1() Dim db As dao.Database, tdf As dao.TableDef, fld As Field Set...
by: lineone | last post by:
I have two tables The first, MainTBL, has the following fields: ID DefFIRST DefMI DefLAST DefSUF DefDOB
by: BikeToWork | last post by:
Is there some easy way to "flatten" the multiple values from a multi-value field? I should state at the outset that I hate multi-value fields, but unfortunately they were used and I need to retrieve...
by: Aliciasmith | last post by:
In an age dominated by smartphones, having a mobile app for your business is no longer an option; it's a necessity. Whether you're a startup or an established enterprise, finding the right mobile app...
by: giovanniandrean | last post by:
The energy model is structured as follows and uses excel sheets to give input data: 1-Utility.py contains all the functions needed to calculate the variables and other minor things (mentions...
by: NeoPa | last post by:
Hello everyone. I find myself stuck trying to find the VBA way to get Access to create a PDF of the currently-selected (and open) object (Form or Report). I know it can be done by selecting :...
by: Teri B | last post by:
Hi, I have created a sub-form Roles. In my course form the user selects the roles assigned to the course. 0ne-to-many. One course many roles. Then I created a report based on the Course form and...
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 1 Nov 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM) Please note that the UK and Europe revert to winter time on...
by: nia12 | last post by:
Hi there, I am very new to Access so apologies if any of this is obvious/not clear. I am creating a data collection tool for health care employees to complete. It consists of a number of...
by: NeoPa | last post by:
Introduction For this article I'll be focusing on the Report (clsReport) class. This simply handles making the calling Form invisible until all of the Reports opened by it have been closed, when it...
by: isladogs | last post by:
The next online meeting of the Access Europe User Group will be on Wednesday 6 Dec 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, Mike...
by: GKJR | last post by:
Does anyone have a recommendation to build a standalone application to replace an Access database? I have my bookkeeping software I developed in Access that I would like to make available to other...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.