Count checked Check Boxes on a report 1

[paqguy]

Is there a way to count the number of checked check boxes on a report. The check box is based off a boolean expression, not a yes/no field.

Thanks. paqguy - Shaun Beane

 

[AutumnBlues]

Provided your checkbox names always begin "chk", you could try something like this.
------------------
Dim x as integer
Dim ctrl as control

x = 0
For Each ctrl In Me.Controls
If Left(ctrl.ControlName, 3) = "chk" Then
If ctrl.value = true then x = x + 1
End If
Next
-----------------

So x = the number of checked boxes

Hope it helps
AutumnBlues

 

[MichaelRed]

Hmmmmmmm,

M. Autum's suggestion (or variations of hte theme) could be applied to each record to generate the number of checked items within a single record. What remains is to do the summation of these throughout the report. This may be accomplished in several ways, but the choice would be influenced by the overall reporing requirements.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[RobPotts]

Try:
=Sum(iif([Name of Check Box]=-1,1,0))

-1 seems to indicate a checked box, from what I can tell.
That works on my reports.
Hope this helps
Rob!

 

[VogonPoet]

A variation on RobPotts theme:

=sum(abs([nameofcheckbox]))

Michael

 

[MichaelRed]

Still no one going to ask / address the question re doing this for multiple check-boxes per record or just totalizing the results per field ... other ... both ... ???

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[AutumnBlues]

MichaelRed, if I were trying to total over many pages (records), I'd probably work with the underlying data (normally a query), rather than the report itself.

 

[RobPotts]

Hi Michael,

For counting the number of checked boxes in a feild you could just put something like

=(Abs([CB1]))+(Abs([CB2]))+(Abs([CB3]))etc for all check boxes in that record

You could then add this to a form or a report in the detail section.

Hope this answers your question, or have I just missed the point
Rob

 

[danvlas]

It does not matter whether the check box is based on a field or expression. What is important is that the value is always 0 (not checked) or -1 (checked).

I'll start from the end-meaning Michael's point.
Assuming we have solved the problem of counting the checked boxes in a record and we have this result in RecTextBox, let's create one text box RunningChecked in the same section that will have the control source:
=RecTextBox
In the report footer section, let's make another text box TotalChecked with the control source:
=RunningChecked
Set RunningChecked's Visible property to False and there it is...

Now, the beginning (but if I were a bat, I'd say it's the beginning)

Since in every record you have a known limited number of check boxes, then you can set RecTextBox's control source to:
=0-Nz([CheckBox1],0)-Nz([CheckBox2],0)...-Nz(CheckBoxN],0)

If the number of check boxes is unknown, then you will have to use code:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Dim ctr As Control
For Each ctr In Section(0).Controls
If ctr.ControlType = acCheckBox Then
RecTextBox = Nz(RecTextBox, 0) - Nz(ctr, 0)
End If
Next
End Sub

Good luck

Daniel Vlas
Systems Consultant
danvlas@yahoo.com

 

[MichaelRed]

O.K. At last. Autum has finally "clarified" the problem -and Dan provides a real soloution!

A slightly different approach would be to use a general purpose soloution for the sum of a group of numeric values (basSum) shown below. Of course with check boxes you need a small bit of additional work. Because the check box values are actually true/false (negative/zero)m their 'summation' needs the sign reversal to give an actual count. So, assuming three check boxes have the following Names:

chkThis; chkThat; chksomethingElse

The expression =abs(basSum(chkThis, chkThat, chksomethingElse)

should do nicely

Public Function basSum(ParamArray varMyVals()) As Variant

    'Michael Red 3/7/2002
    'To return the SUM of a series of values

    'Sample Usage:
    '? bassum(1, 2.0067, 3)
    '6.0067

    Dim Idx As Integer
    Dim MySum As Variant

    For Idx = 0 To UBound(varMyVals())
        If (IsMissing(varMyVals(Idx))) Then
            GoTo NextVal
        End If
        If (IsNumeric(varMyVals(Idx))) Then
            MySum = MySum + varMyVals(Idx)
        End If
NextVal:
    Next Idx

    basSum = MySum

End Function

M. Autum, since I have not seen previous posts by you, I am not familiar with your level of knowledge. Thus I wanted to be CLEAR about the problem you wanted help with. In many threads, I have seen (and occassionally contribuited to) answering SOME question in great detail -only to later realize/find out that the answer aoolied to something other than the actual question. While Tek-Tips ADVERTISES that it is a forum for "PROFESSIONAL[/]" programmers, the sphere abounds with students and others with less than 'professional' level of knowledge. Except in rather blatant instances, these are welcome, and responses are often provided. However this poses the issue that the level of knowledge is not always aparent, with your original post being a good example of ambiguity. Not many real professionals would need help with the field summation, however many of the less qualified would ask the question. I try to be somewhat more conservative than many who contribute to these forums, and often would ignore posts like yours, as reflecting a level of understanding of even how to properly (or completly) post the question. I decided to attempt to generate some additional discussion just to spare the posting of numerous replies which answered the 'other' question.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[AutumnBlues]

That's an interesting piece of code. Personally, I'd prefer to work with underlying the data rather than perform calculations on calculations.

I don't know the ins and outs of the original report, but normally it would be based on some solid data sets. Rather than trying to sum or count checked checkboxes on a report that is based on a query that is based on one or more tables (and/or other queries), I'd just look back at the original data.

An extra field in the query might show the number of 'true' values for each record. And a piece of SQL might provide the sum.

It seems to be a common theme: treating the symptoms of a problem with clever and convoluted solutions, when a simple answer will do if the root of the problem is addressed. I'm as guilty as the next guy.

AutumnBlues

 

[MichaelRed]

M. Autum

Actually, my soloution 'works' equally well with either the underlying recordset as it does with the report controls. Also, danvlas' soloution -with a moderate revision- would also be useful as a routine working on aginst the underlying 'recordset'.

On the other hand, I don't even see (much less agree) that the soloutions are " ... symptoms of a problem ... " as opposed to reasonable soloutions.

One of the early lessons in the relational database processing is to layer queries to solve even moderatly complex issues. Layering just breaks a problem into more easily managed chunks. Manipulating data in the recordset or recordsource or controls within a report is not any different than layering of queries in the broader context.

If, by " ... an interesting piece of code ... " you are implying that you would consider using it in your application(s), thanks for the implied compliment, If, on hte other hand, you mean it in the sense (context) of that which might be carefully studied from afar, well to each their own.

finally, of course, we have STILL not heard from Shaun Beane, who posted the original question. So we are STILL not sure the (real / original) question has been resolved.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[AutumnBlues]

Layering can help manage problems, but in this case it's misguided layering that causes the problem. You only need one simple, concise piece of SQL. (Might be a tad quicker too!)

(I just meant that it's an interesting piece of code, that's it really.)

AutumnBlues

 

[MichaelRed]

" ... misguided layering that causes the problem ... "

is the question. Exactly WHAT is misguided? What is really inappropiate to either soloution? Or -who appointed you to the code cops?

I'm not trying to small minded, obtuse, obdurate, mean, ... whatever, BUT (the inevitable BUT ... ) I truly do not see or undestand your objection. If you are so much better than I at this, I would apprecite the insight and help. On the other hand, to simply make your statetment - without even rationale (much anything approaching "proof&quot is not sufficient for me to convert to AutumnBluesism. Again, I note this is our first 'meeting' (at least to my memory). There are some here, who have shown their expertise over a range of subjects and period of time - and who's opinions and suggestions I might easily accept with little or no hesitation. At least to this point, I have not adopted this attitude toward you. Even if I had, I would possibly still persue the greater insight you (purport to) offer.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[AutumnBlues]

If you can't receive opposing ideas without becoming over-emotional, you shouldn't enter into debate. Sneering comments are not appreciated - others have expressed the same opinion.

Quit talking down to people, lose the arrogant tone and lighten up.

AutumnBlues

 

[VogonPoet]

AutumnBlues,

Thank you.

By the way, I agree with your basic concept of keep it simple.

Michael

 

[MichaelRed]

To each his own, i guess. I asked for the support / rationale/ insight and this is the response?

I did not characterize this as a debate, but requested something besides your 'opinion' to support the point of view, after stating that I do not see the difference. If this is the support of your opinion ...

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[danvlas]

OK, since my mailbox is 'bombarded' with replies to this post, I'll get back in and give my personal opinion about each (including my own) solution.

AutumnBlues: Your solution implies coding. From the original question, we cannot detect poster's level of expertise in coding. Trust me, there are people who are able to create and use forms and reports, but they don't even know about the code behind them.
Moreover, Michael was absolutely right in saying your code would be valid for one record only. That's a fact and you should gracefully admit it.
Besides...why do you want to force the report creator to name the checkboxes in a certain way? Or what if there is a text box name that starts with 'chk'??? There is a ControlType property, you know...
You personally prefer to work with underlying data. That's fine. I personally prefer to have the underlying query to be as simple as possible and provide only the absolutely necessary data to the form/report. And I'm what you may call a 'calculation extremist'. Whenever I have to choose between including a calculated field in a query and placing a calculated control on the form/report, I choose the latter. I have encountered 'The query is too complex' messages in a few instances and I learnt my lesson. But that's just a matter of personal style (and charm, of course ).

RobPotts and VogonPoet: If the checkboxes are based on expressions, how can you sum them by name (of course I'm speaking of multiple records???) I thought summing the values of calculated controls could be done by entering Sum(EntireCalculationFormulaOfTheControl)...

RobPotts only: I'm sure Michael couldn't stop himself from ing after you taught him how to write an expression to sum some values

Dear Me and Myself...The code provided counts only the checkboxes in the detail section, but what if there are more sections in the report? Could that be part of Michael's " moderate revision"? Lucky me it's just moderate...

Michael: Your solution implies coding too. Moreover, the array argument has to be filled in before calling the function. And that has also to be done in coding.
(And if I may say-but you already know it )You have a really talent in making people cry

Paqguy, where are you?

And finally, whoever gave me that star, thank you

Daniel Vlas
Systems Consultant
danvlas@yahoo.com

 

[paqguy]

Ok, sorry I haven't bee around to read this. I've been onsite teaching for the last few days.

To answer a few questions:
The check box I want to count is in the detail section and there is only one check box - which is based on a calculated control, comparing date differences. I just need to count the yes and nos. I have done this with a yesno field using a calculated control in the footer with something like iif(sum([field]),1,0) - I think - and it works fine. This same code doesn't work if the check box is based on a calculated control.

I do have coding experience, so I'm going to try a couple of the suggestions here - thank you. I won't be back in the office until Monday, unfortunately.
paqguy - Shaun Beane