Calculating Multiple Checkboxes with If Statements

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have four different fields that are yes/no checkboxes in my report. The
fields are chk_firstAid, chk_physio, chk_FDvisit, and chk_SHMHvisit.
At the end of the report I will do a sum of all four checkboxes, however the
user wants me to set it up with a tricky multiple if statement that I can't
wrap my head around and actually don't even know if it is possible.
In each record one or all of the four checkboxes can be checked. For
different senarios the user wants the checkbox sums to total up differently.
For example if chk_firstAid and chk_physio checkboxes are both checked for
one record then I just want the chk_physio total to add one to the sum at the
end of the report and not add one for the total for chk_firstAid. Is this
possible and if so how can I code this?
 
Well, You would ahve to give us your complex scenario.

If it were me, I'd add a new unbound field to he detail section and have it
figure out how many to add for any given record using my if statement.
Then, I'd just sum that field at the end.

Rick B
 
Rick said:
Well, You would ahve to give us your complex scenario.

If it were me, I'd add a new unbound field to he detail section and have it
figure out how many to add for any given record using my if statement.
Then, I'd just sum that field at the end.
Click to expand...

I see that I'm breaking into a thread--I thought I answered a first
post. Apologies if I drag on. I seem to have similar thoughts, see below.
Playa wrote:
Click to expand...

This leaves me with the impression your data is not in units. If a check
may have different interpretations depending on other entries in that
record, the combination may actually a bad representation of some option
list.

It can be done and is not difficult; but it requires that you have a
table showing all relevant combinations with their interpretation. (That
can take quite some time with your customers to find out.) You join this
table to your data (query, I presume) and sum the interpretations.
 
Ya I acutally thought about doing it like that the problem is how do I code
the If statements on the report?

I will try and explain the full scenario.

Four checkboxes in my report are....

First Aid [ ] Physio[ ] Visit FD [ ] Visit SMGH [ ]

At the end of the report I will have a Totals textbox for each of the
checkboxes. For each record one, some or all of these may be checked yes or
true.

One case scenario is just one of the checkboxes are checked.
First Aid [X] Physio[ ] Visit FD [ ] Visit SMGH [ ]
Therefore for this record the Total Sum textbox for this First Aid checkbox
would add one.

Another Case scenario is if two of these were checked.
First Aid [X] Physio[X] Visit FD [ ] Visit SMGH [ ]
Therefore for this record Since Physio supercedes First Aid, I just want the
Physio checkbox to get added to the Totals at the botten and not the First
Aid.

If three were checked off the checkbox that is furthest to the right would
get 1 added to the total at the end of the record. so on and so on.

Sorry if this is hard to follow it is hard for me to explain. So doing it
in the detail section and figuring out how to add for any given record does
make good sense. then i can just total those up at the end. I am just now
sure how to do this in the detail section of a report with proper if
statements.
 
Playa said:
Ya I acutally thought about doing it like that the problem is how do I code
the If statements on the report?

I will try and explain the full scenario.

Four checkboxes in my report are....

First Aid [ ] Physio[ ] Visit FD [ ] Visit SMGH [ ]

At the end of the report I will have a Totals textbox for each of the
checkboxes. For each record one, some or all of these may be checked yes or
true.

One case scenario is just one of the checkboxes are checked.
First Aid [X] Physio[ ] Visit FD [ ] Visit SMGH [ ]
Therefore for this record the Total Sum textbox for this First Aid checkbox
would add one.

Another Case scenario is if two of these were checked.
First Aid [X] Physio[X] Visit FD [ ] Visit SMGH [ ]
Therefore for this record Since Physio supercedes First Aid, I just want the
Physio checkbox to get added to the Totals at the botten and not the First
Aid.

If three were checked off the checkbox that is furthest to the right would
get 1 added to the total at the end of the record. so on and so on.
Click to expand...

So you want to count the furthest to the right? Your unbound controls'
source could be <was to write something> wait: I suggest you have four
hidden textboxes. Every textbox should only count its checkbox if and
only if all checkboxes to the right are clear.

So, from right to left:

=abs(visitSMGH) <that was easy>
=iif(visitSMGH,0,abs(visitFD))
=iif(visitSMGH Or visitFD,0,abs(physio))
=iif(visitSMGH Or visitFD Or physio,0,abs(firstaid))

I'm doing these abses :-) because you want to sum. It is not
necessary--you can have -sum() in the end. Yeah, do that, it is easier
on the eyes.

My suggestion with an interpretation table would work too, here. But I
can't remember the SQL I wrote then so take it this way. Interested,
nevertheless? I can look it up if you want.
 
Back
Top