I'm trying to display some address information on a billing invoice like so...

Name
CompanyName
AddressLine1
AddressLine2
City, State Zip

If AddressLine2 doesn't have any data, I want to print City, State, and Zip in it's place. I created this expression which doesn't have any syntax issues but displays #Error when the report is generated. I also tried adding Table and List Item data regions and removing the dataset from the expression but it still returns #Error.

Since #Error isn't very intuitive, I was wondering if anyone could tell me what is wrong with my expression.

Thanks.


=IIF(Len(First(Fields!AddressLine2.Value, "STMTACCTINFO") > 0), First(Fields!AddressLine2.Value, "STMTACCTINFO"), First(Fields!City.Value, "STMTACCTINFO") & ", " & First(Fields!State.Value, "STMTACCTINFO") & Space(1) & First(Fields!ZipCode.Value, "STMTACCTINFO"))

What is the definition of first function. You debug each component of first used in your statement.

urt

Taking out the Len() function and checking for the value = "" works. This wouldn't work of course, if the field was returning NULL but then you should be able to use the IsNothing() function instead.

I'm sure there's a way to use the Len() function without error and I would still like to figure it out but for now I'm going with what works.


=IIF(First(Fields!AddressLine2.Value, "STMTACCTINFO") = "", First(Fields!City.Value, "STMTACCTINFO") & ", " & First(Fields!State.Value, "STMTACCTINFO") & Space(1) & First(Fields!ZipCode.Value, "STMTACCTINFO"), First(Fields!AddressLine2.Value, "STMTACCTINFO"))

I would hide the row if Address2 is null. It is easy and you can save a lot of time typing Iif.