Counting check boxes that are checked on a report footer line.

[RangerFan]

I am attempting to add a total on my report footer line containing a count that reports whether a check box on each detail line generated by the report has in fact been checked. I am trying to use the COUNT function in my Control Source, but am somewhat confused as to the syntax required to determine whether the check box reads as TRUE of FALSE. Using Access 2010. Any help would be greatly appreciated.

 

[LaurieHamlin]

A true checkbox should = -1 and false should = 0.
Hope that helps.

 

[RangerFan]

Hi Laurie and thanks for your reply. I believe that my problem is with the syntax I am using for the Text Box Control Source. This is what I have: =Count("[Uses_Food_Pantry] = 0"). I know that isn't correct, but am not sure what to try next. This is one of those things that I have not had to do before and I'm a little stuck. Again, thanks for your help.

 

[lameid]

If you want to see how many times a field / column named Uses_Food_Pantry is checked...

=Sum(IIF([Uses_Food_Pantry] = -1, 1,0))

The IIF function takes a logical test as the first argument and returns the second parameter if true or the third if false. So if it is checked (=-1) then 1 otherwise 0.
It is works exactly the same as the Excel IF function if that helps.

This is easy to reverse by changing the -1 to 0 if you want the count of unchecked items.

I'd also try tinkering with replacing the -1 with true or yes (I don't remember off the top of my head which will work in a report)... It could happen that MS decides to store true differently in the future and assumptions about -1 = True / Yes could break it. Also in SQL server Yes/No columns become bit and they are 0/1 not 0/-1 like in Access so it is good habit to think in terms of logical test.

 

[dhookom]

I would count the number checked with this expression:

=Sum(Abs([Uses_Food_Pantry]))

Duane
Hook'D on Access
MS Access MVP

 

[lameid]

Well if your going to go with -1 is true as an assumption (and it proabably will be forever) I'd go with...

=Abs(Sum([Uses_Food_Pantry]))

That way you are only calling ABS once as opposed to for each record. I doubt you'd ever notice the difference but just in case.

 

[RangerFan]

The solutions worked out just fine. Thank you all for your help. Greatly apprecited!!!