How to Count Multi Value Field

[Bhagaban]

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.

[NeoPa]

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.

[isladogs]

@NeoPa
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.

@Bhaghaban
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

SELECT tblMVF.ID, Count(tblMVF.MVF_Field) AS CountOfMVF_Field
FROM tblMVF
GROUP BY tblMVF.ID;
 

[NeoPa]

IslaDogs:
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.

[isladogs]

Yes, I avoid them as well. In fact I wrote this article Multivalued fields... and why you really shouldn't use them

[NeoPa]

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.