Combining Field Entries when One Value is Empty

How do you combine the following fields into the one field in a report each separated by a comma.

Address1, Address2

How to avoid the extra comma if the second of the fields is empty.

To do this, an IIf statement maybe the way to go.

In an unbound text box on a report, add the following:

=[Address1] & IIF(IsNull(Address2)," " ,", " & [Address2])

What the IIf statement does is display the first option if the statement is true and the second option if the statement is false. There fore, in the above IIF statement, if [Address2] is null, then it would display a space, but if it was not null it would display a comma, then a space, then the field value. Repeat for each field if there is more than two fields.

If you are not getting the results you expect you may need to take into account a zero length string – i.e. the field contains the value “”, as opposed to a null field.

In this case your IIF statement would look like:

FullAddress:[Address1] & IIF(IsNull(Address2) Or [Address2] = ""," ",", " & [Address2])

Do you like this tip? Subscribe to my free weekly Newsletter to receive tips weekly via email. Click Here to subscribe.