IIf for individual record

J

jac

I am trying to count and add the occurrences of "No" that occur in selected
table fields for individual records on a form. The expression below is
working for me to some extent, but counts the field values for the entire
database and not for each individual record. How can I correct this
expression to make it work the way I want?

=Sum(IIf([A2]="No",1,0)+IIf([A2a]="No",1,0)+IIf([A7]="No",1,0)
IIf([A7a]="No",1,0) IIf([A8]="No",1,0))
 
R

Rick Brandt

jac said:
I am trying to count and add the occurrences of "No" that occur in
selected table fields for individual records on a form. The
expression below is working for me to some extent, but counts the
field values for the entire database and not for each individual
record. How can I correct this expression to make it work the way I
want?

=Sum(IIf([A2]="No",1,0)+IIf([A2a]="No",1,0)+IIf([A7]="No",1,0)
IIf([A7a]="No",1,0) IIf([A8]="No",1,0))
Click to expand...

Get rid of Sum(). Sum() works against rows, not arguments.

You're not in Excel any more Toto :)
 
J

Jac

THANK YOU! It worked!

Rick Brandt said:
jac said:
I am trying to count and add the occurrences of "No" that occur in
selected table fields for individual records on a form. The
expression below is working for me to some extent, but counts the
field values for the entire database and not for each individual
record. How can I correct this expression to make it work the way I
want?

=Sum(IIf([A2]="No",1,0)+IIf([A2a]="No",1,0)+IIf([A7]="No",1,0)
IIf([A7a]="No",1,0) IIf([A8]="No",1,0))
Click to expand...

Get rid of Sum(). Sum() works against rows, not arguments.

You're not in Excel any more Toto :)
Click to expand...
 
J

Jac

Ok. Now I'm really going to show my access ignorance. The calculated values
are showing up and working correctly on my form. They are not, however,
being input into the database for that field (all fields remain blank). I
want to be able to run a mail merge document linked to the database and
display the results of this calculation. How do I get the form values to
transfer into a database?

jac
 
R

Rick Brandt

Jac said:
Ok. Now I'm really going to show my access ignorance. The
calculated values are showing up and working correctly on my form.
They are not, however, being input into the database for that field
(all fields remain blank). I want to be able to run a mail merge
document linked to the database and display the results of this
calculation. How do I get the form values to transfer into a
database?
Click to expand...

What field? A control saves a value into the form's RecordSet table by having a
ControlSource that is the name of a field in that RecordSet. What is your
ControlSource? It's an expression. A control can either contain an expression
OR save its value to the underlying table. It cannot do both.

Now for the good news. Calculations should not be saved anyway. Just build a
query based on your table with the expression as a calculated field and then use
the query for the mail merge.
 
Top