Combining Field Entries
Combining Field Entries. 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.
When combining field entries, 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])
