fields blank

  • Thread starter Thread starter rml
  • Start date Start date
R

rml

I'm using the following code to combine four fields. It works fine. My
problem is when all the fields are blank I get #Error on the report for that
record. What I would like when all fields are blank is "NA" to be placed.

Thanks.

=Left(IIf(IsNull([np1]),"",[np1] & ", ") & IIf(IsNull([np2]),"",[np2] & ",
") & IIf(IsNull([np3]),"",[np3] & ", ") & IIf(IsNull([np4]),"",[np4] & ",
"),Len(IIf(IsNull([np1]),"",[np1] & ", ") & IIf(IsNull([np2]),"",[np2] & ",
") & IIf(IsNull([np3]),"",[np3] & ", ") & IIf(IsNull([np4]),"",[np4] & ",
"))-2)
 
You might be better off using

([np 2] + ", ") & ([np 2] + ", ") & etc

If you use a + sign in brackets, and a field within the brackets is Null,
then all the stuff within the brackets becomes null

Then you can use =NZ(([np 2] + ", ") & ([np 2] + ", ") & ([np 2] + ", "),
NA)
Evi
 
Back
Top