×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Can I count selected values in a multivalue lookup field?

Can I count selected values in a multivalue lookup field?

Can I count selected values in a multivalue lookup field?

(OP)
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.

RE: Can I count selected values in a multivalue lookup field?

(OP)
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...

RE: Can I count selected values in a multivalue lookup field?

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.

RE: Can I count selected values in a multivalue lookup field?

(OP)
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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close