Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Can I count selected values in a multivalue lookup field?

Status
Not open for further replies.

Jtorres13

Technical User
Nov 11, 2006
162
US
I have a form containing a combo box. The control source is a SELECT statement, grabbing values from a table field called Users. The combo box works well. The combo box is a multivalue lookup field. It has check boxes next to each value. Checking the boxes for different user names, puts those names in a paragraph, which I can see without dropping the list of values. This is all working OK. I drop the list of names, check the boxes and when I exit the combo box, the names I selected show in a paragraph.

If I check only 2 or 3 names, no problem, I can take a look and quickly count. Sometimes there are dozens of names being displayed and I have to stop and count them all by hand if I want to know the count of names...

My question is, how can I count how many boxes have been checked (how many user names are being displayed in this Lookup Field/Combo Box)?

Is there any way to get a calculated total?

Thanks.
 
I think I got it!!! The control source for the form was a table. I created a query, added the table and made a calculate field (GROUP BY "Title", Count of "Multivalue LookupField". I'm testing now and will let you know if it works. I hate multivalue fields...
 
From what you describe (checkboxes) I assume this is a multivalue field. Access does not have a control with checkboxes except the multi value field control, unless using some non-native control. If that is the case you can create a query that counts the values. When you look at the table in a query design view do you see a plus sign next to users.
+ Users
If you click on the + sign do you see something like
- Users
Users.value

If so you have a MV.
Lets assume you have a Primary key called SomeID. Then you can create a query.
Code:
SELECT 
 SomeID, First([Users]) as SelectedUsers, Count(Users.Value) AS NumberUsers,
FROM Users
GROUP BY SomeID

Multivalue fields are like a table in a table. So the individual selected users are stored in a table that you cannot see and the field "Users" is a concatenation of those individual records.
 
Creating a query with a calculated field and then basing the form on the query worked great. Leaving the answer here in case someone else needs it in the future.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top