Simple Combo Box Multiple Select Question

[ahamilton]

Hello all you expert Access users! I am new to Access and have what I think is a very simple question that I can not seem to solve. I have a training record form that allows you to select those who attended by checking boxes in a combo Box. I would like a field in my report that tells me the total number in attendance by counting the number of checked boxes in the combo box. What expression, formula, and/or validation rule do I use??

[pkstormy]

In the recordsource query, add an expression as such: MyExpressionName: IIF([MyCheckBoxField] = True,1,0). Add this MyExpressionName to the report and then sum it in the footer.

Or you can create a query which uses the above expression and then another query based off this one which sums all the 1's (in MyExpressionName) and use the Dlookup command on the report to retrieve the value from the summed query.

[ahamilton]

Thank you pkstormy!! Near as I can tell, I have the simplist DB ever. And you provided a solution to my problem. But, I unfortunately cannot get it to work. Very frustrating. Thanks anyway.

[pkstormy]

What are you stuck on?

[ahamilton]

Good morning pkstormy. When I add the expression IIF([MyCheckBoxField] = True,1,0), I get the message "The multi-valued field cannot be used in a Where or Having clause". I'm pretty sure that the problem is my lack of understanding the software and have come to the conclusion that a class would be extremely helpful. Those of you that have a comprehensive understanding of Access must find the rest of us exaspirating.

[pkstormy]

Let me think....

What is the actual field name of your field for [MyCheckBoxField] and is this a bit field type? (you need to change MyCheckBoxField to whatever your field name is.)

You may want to pick up the MSAccess Developers Guide book by Sybex/Ken Getz. This book is better than any class, expecially for writting vba expressions. Classes don't really get that much into expression equations that much - they're more of a general guide on creating forms/queries/reports/etc...

[ahamilton]

Good afternoon pkstormy,

Thank you for your steadfast attempt to help me!! The name of the combo box multiple select field is "Attendees" which uses an employee table to provide a list of names with boxes to check if they attended the training session (I did use the name of my field in the expression you provided). I was hoping that one of my reports would show the total number of attendees for each training session - based of course on the field counting the number of checked boxes in the combo box multiple select field. I must admit that I am confused by recordsource, queries, Dlookup commands, and such. Even so, I think that I followed your initial instructions properly. I will definately pick up the book you recommended; and I'm thinking a course prividing a general guide on creating forms/queries/reports/etc..."Access for Dummies" kinda thing might help me Thanks again.

[pkstormy]

You can also hit the F1 key for help on any property. You may also want to study up on a form's recordsource, different kinds of queries, and the dlookup command. These are some of the most common things to know.

If you need help, you can also have some fun with this:
https://www.accessforums.net/sample-...ardy-7506.html

The code repository on this site also has a lot of examples to look at. I did a lot of my learning by looking at examples by others.
https://www.accessforums.net/code-repository/