Error in iif statment

[Guest]

Hi,
I am trying to fill up one field with sum of iif expressions taken from few
fields in a form. Some of the field values may have null. Hence, I would like
to use iif function to use the value zero when any field has null value.
However, the expression comes witht the error "The expression you entered
does not have a valid syntax"

The CODE is:

=iif(([age00_04],[age00_04], 0) +iif([age05_09], [age05_09], 0) +
iif([age10_12], [age10_12], 0) + iif([age13_15], [age13_15], 0) +
iif([age16_18], [age16_18], 0) + iif([age19_99], [age19_99], 0))

Any help is appreciated. Thanks

 

[fredg]

Hi,
I am trying to fill up one field with sum of iif expressions taken from few
fields in a form. Some of the field values may have null. Hence, I would like
to use iif function to use the value zero when any field has null value.
However, the expression comes witht the error "The expression you entered
does not have a valid syntax"

The CODE is:

=iif(([age00_04],[age00_04], 0) +iif([age05_09], [age05_09], 0) +
iif([age10_12], [age10_12], 0) + iif([age13_15], [age13_15], 0) +
iif([age16_18], [age16_18], 0) + iif([age19_99], [age19_99], 0))

Any help is appreciated. Thanks

I believe the first parenthesis is incorrect placed.
You can omit it and the last one also.

But then, no IIf's are needed.
Look up the Nz() function in VBA help.

=Nz([age00_04],0) + Nz([age05_09],0) + etc.

 

[Guest]

Thanks for the help Fred. With the changes, the syntax error is gone.
However, I am seeing #Name? in the calulated field. I checked the reference
to the different fields. Apparently those are correct. Any thoughts why the
display is showing #Name?

 

[fredg]

Thanks for the help Fred. With the changes, the syntax error is gone.
However, I am seeing #Name? in the calulated field. I checked the reference
to the different fields. Apparently those are correct. Any thoughts why the
display is showing #Name?

Hi,
I am trying to fill up one field with sum of iif expressions taken from few
fields in a form. Some of the field values may have null. Hence, I would like
to use iif function to use the value zero when any field has null value.
However, the expression comes witht the error "The expression you entered
does not have a valid syntax"

The CODE is:

=iif(([age00_04],[age00_04], 0) +iif([age05_09], [age05_09], 0) +
iif([age10_12], [age10_12], 0) + iif([age13_15], [age13_15], 0) +
iif([age16_18], [age16_18], 0) + iif([age19_99], [age19_99], 0))

Any help is appreciated. Thanks

1) Make sure the name of each control is not the same as the name of
the field in it's control source expression.

2) Did you precede the expression with an = sign?
=Nz([FieldName],0)+ etc.

3) Are you sure you correctly spelled each field name and that that
field does exist?

4) If you still have a problem, post the entire expression and the
control's name.