IIF(Iserror()) Help

A

Adam

Hi All,

I have the following formula in a form field:

=([Skills - Achieved]+[Process & Systems - Achieved]+[Knowledge -
Achieved]+[Adding Value - Achieved]+[Case Management -
Achieved]+([ac1]*100)+([ac2]*100)+([ac3]*100)+([ac4]*100)+[Attendance -
Achieved]+[Punctuality - Achieved]+[Dress code -
Achieved]+[Housekeeping - Achieved]+[Team work - Achieved])/14

Unfortunately if all of the fields are not filled in then this gives me
a "#Error" error in the field.

I've tried to use the IIF(Ierror(formula),"",(formula)) function
however this returns "#name?" error.

Can anyone help me with this please?

I just don't want anything to be displayed in the field if there is
going to be an error shown there.

Regards

Adam
 
B

BruceM

Are you trying to add up something, or are you trying to concatenate a
series of fields and formula results? Where in the "form field" is the
formula? One possibility is that the Nz function would take care of the
empty fields if this is a sum:

=Nz(Sum([Skills - Achieved] ...))/14

I will just add that you aren't doing yourself any favors by using long
field names that include non-alphanumeric characters. Maybe you can get
away with it, but it makes formulas and expressions needlessly complex and
confusing.
 
P

Pat Hartman\(MVP\)

You need to use the Nz() function as was already mentioned but the example
was incorrect. The Nz() needs to enclose the individual fields otherwise
the entire expression will return zero if one element is null.
=(Nz([Skills - Achieved],0) + Nz([Process & Systems - Achieved],0) +
Nz([Knowledge - Achieved],0) + .....

The Sum() isn't necessary unless you are aggregating the data.

And finally, ditto on the names. Best practice is upper/lower case letters,
numbers, the underscore, and NOTHING else. No embedded spaces, no dashes,
etc.
 
Top