Calculating Multiple Checkboxes with If Statements

[Playa]

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?

 

[Rick B]

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

 

[Bas Cost Budde]

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.

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:

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.

 

[Playa]

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.

 

[Bas Cost Budde]

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.

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.

 

[Playa]

Hey thanks I am actually trying what you have done here and am getting a
syntax error when I put the =iif(visitSMGH,0,abs(visitFD)) in my unbound text
box. I have tried different things but can't get nothing to work. Is
something mistyped?

 

[Bas Cost Budde]

Hey thanks I am actually trying what you have done here and am getting a
syntax error when I put the =iif(visitSMGH,0,abs(visitFD)) in my unbound text
box. I have tried different things but can't get nothing to work. Is
something mistyped?

Dunno. Is the comma your item separator? Is it really? Is it too in
reports? In controlsources for controls on reports? (I'm not all
kidding, I find locale settings have strange effects)

You're putting this in the Controlsource, right? does this work (I mean,
yield 1 in preview):

=iif(true,1,0)

?

Are your fields called visitSMGH and visitFD? Are the *controls* also
called that way?

Hm... syntax error, you say. How does that manifest? I had #name and
#error in reports, in the preview; I had complaints about unmatching
brackets where Access doesn't want me to leave the expression (always
better than the VAC that states "missing parenthesis ADDED").

 

[Playa]

Hey I actually got that to work, i was missing these [ ]. Thanks for all
your help, that was a very quick and easy soloution. One more question if
you don't mind. How do I now total all those textboxes for all the records
at the end of the report?

 

[Bas Cost Budde]

Hey I actually got that to work, i was missing these [ ]. Thanks for all
your help, that was a very quick and easy soloution. One more question if
you don't mind. How do I now total all those textboxes for all the records
at the end of the report?

have some control in the end (report footer?) with
=sum(mytextboxcontrolname).

If that doesn't work (somehow I hesitate), you may try to add another
four controls to the detail, with running sum

 

[Playa]

For some reason when i try to sum in the Report Footer section it never
works. It works in the detail section but that is no good to me. I don't
want to see the total after every record I only want to see the grand total
after the last record. Is there a special way to get the sun in the report
footer?

:

 

[Playa]

Never mind I actually figured it out. Thanks for all your help.