Summing iifs

[cmtrain via AccessMonster.com]

I am using an iif statement to determine when any one of eight option buttons
are set to Yes. I then want to do a sum or count to find the total of records
that have all Nos. I am using an unbound field in a form to find whether the
options are all Nos or have at least one Yes:

=IIf(([F&C-V]=-1) Or ([EA-V]=-1) Or ([Interp]=-1) Or ([HLR-V]=-1) Or ([F&C-N]
=-1) Or ([EA-N]=-1) Or ([Tech-N]=-1) Or ([Family Group]=-1),0,1)
This works.

When I add a Sum or Count to this statement and place it in the Report Footer,
I get a #Error message. Any ideas on what I'm doing wrong? Thanks.

=Sum(IIf(([F&C-V]=-1) Or ([EA-V]=-1) Or ([Interp]=-1) Or ([HLR-V]=-1) Or (
[F&C-N]=-1) Or ([EA-N]=-1) Or ([Tech-N]=-1) Or ([Family Group]=-1),0,1))

 

[Jeff Boyce]

It may just be a matter of semantics, but "option buttons" in Access are
one-of-a-group choices ... there wouldn't be "eight option buttons", and
none of them would have a value of -1. I'll assume you are describing
"checkboxes" (i.e., true/false fields that have a value of -1 when "true").

You know your situation ... can only one of those conditions be true at a
time, or could all eight? "How" will depend on "what"...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[BruceM]

It seems these are eight separate fields rather than an option group in
which only one option can be selected.

If you are trying to count the number of checked check boxes (Yes/No fields
set to Yes or True), try:

=Abs([F&C-V] + [EA-V] + [Interp] + [HLR-V] + [F&C-N]
+ [EA-N] + [Tech-N] + [Family Group])

 

[cmtrain via AccessMonster.com]

It may just be a matter of semantics, but "option buttons" in Access are
one-of-a-group choices ... there wouldn't be "eight option buttons", and
none of them would have a value of -1. I'll assume you are describing
"checkboxes" (i.e., true/false fields that have a value of -1 when "true").

You know your situation ... can only one of those conditions be true at a
time, or could all eight? "How" will depend on "what"...

Regards

Jeff Boyce
Microsoft Office/Access MVP

I am using an iif statement to determine when any one of eight option
buttons

[quoted text clipped - 15 lines]

=Sum(IIf(([F&C-V]=-1) Or ([EA-V]=-1) Or ([Interp]=-1) Or ([HLR-V]=-1) Or (
[F&C-N]=-1) Or ([EA-N]=-1) Or ([Tech-N]=-1) Or ([Family Group]=-1),0,1))

Thanks, Jeff.
I am using eight sets on options buttons and wanting to sum the results.

 

[cmtrain via AccessMonster.com]

It seems these are eight separate fields rather than an option group in
which only one option can be selected.

If you are trying to count the number of checked check boxes (Yes/No fields
set to Yes or True), try:

=Abs([F&C-V] + [EA-V] + [Interp] + [HLR-V] + [F&C-N]
+ [EA-N] + [Tech-N] + [Family Group])

I am using an iif statement to determine when any one of eight option
buttons

[quoted text clipped - 15 lines]

=Sum(IIf(([F&C-V]=-1) Or ([EA-V]=-1) Or ([Interp]=-1) Or ([HLR-V]=-1) Or (
[F&C-N]=-1) Or ([EA-N]=-1) Or ([Tech-N]=-1) Or ([Family Group]=-1),0,1))

Thanks, Bruce.
I first want to determine if at least one of the eight fields is checked.
Then I need a total of all records that have none of the eight fields checked.
The iif works to set up the former, I'm not seeing how to do the letter.

 

[cmtrain via AccessMonster.com]

It seems these are eight separate fields rather than an option group in
which only one option can be selected.

If you are trying to count the number of checked check boxes (Yes/No fields
set to Yes or True), try:

=Abs([F&C-V] + [EA-V] + [Interp] + [HLR-V] + [F&C-N]
+ [EA-N] + [Tech-N] + [Family Group])

I am using an iif statement to determine when any one of eight option
buttons

[quoted text clipped - 15 lines]

=Sum(IIf(([F&C-V]=-1) Or ([EA-V]=-1) Or ([Interp]=-1) Or ([HLR-V]=-1) Or (
[F&C-N]=-1) Or ([EA-N]=-1) Or ([Tech-N]=-1) Or ([Family Group]=-1),0,1))

Thanks, Bruce.
I first want to determine if at least one of the eight fields is checked.
Then I need a total of all records that have none of the eight fields checked.
The iif works to set up the former, I'm not seeing how to do the letter.

 

[cmtrain via AccessMonster.com]

It seems these are eight separate fields rather than an option group in
which only one option can be selected.

If you are trying to count the number of checked check boxes (Yes/No fields
set to Yes or True), try:

=Abs([F&C-V] + [EA-V] + [Interp] + [HLR-V] + [F&C-N]
+ [EA-N] + [Tech-N] + [Family Group])

I am using an iif statement to determine when any one of eight option
buttons

[quoted text clipped - 15 lines]

=Sum(IIf(([F&C-V]=-1) Or ([EA-V]=-1) Or ([Interp]=-1) Or ([HLR-V]=-1) Or (
[F&C-N]=-1) Or ([EA-N]=-1) Or ([Tech-N]=-1) Or ([Family Group]=-1),0,1))

Thanks, Bruce.
I first want to determine if at least one of the eight fields is checked.
Then I need a total of all records that have none of the eight fields checked.
The iif works to set up the former, I'm not seeing how to do the letter.

 

[cmtrain via AccessMonster.com]

It seems these are eight separate fields rather than an option group in
which only one option can be selected.

If you are trying to count the number of checked check boxes (Yes/No fields
set to Yes or True), try:

=Abs([F&C-V] + [EA-V] + [Interp] + [HLR-V] + [F&C-N]
+ [EA-N] + [Tech-N] + [Family Group])

I am using an iif statement to determine when any one of eight option
buttons

[quoted text clipped - 15 lines]

=Sum(IIf(([F&C-V]=-1) Or ([EA-V]=-1) Or ([Interp]=-1) Or ([HLR-V]=-1) Or (
[F&C-N]=-1) Or ([EA-N]=-1) Or ([Tech-N]=-1) Or ([Family Group]=-1),0,1))

That did it - Thanks Bruce.