Address Label Problem

[DSmith]

Using this formula in a report:
=([FirstName] & " " & [LastName]) & Chr(13) & Chr(10) & [Address] & Chr(13)
& Chr(10) & [Address2] & Chr(13) & Chr(10) & [City] & "," & [State] & " " &
[zipcode]

If there is no address2 I get a blank line. Anyone know how I can get rid of
the blank line? Already have it marked yes to expand and shrink. Thanks

 

[John Spencer]

Try the following

=([FirstName] & " " & [LastName]) & Chr(13) & Chr(10) &
[Address] & Chr(13) &
Chr(10) & ([Address2] + Chr(13) + Chr(10)) &
[City] & "," & [State] & " " & [zipcode]

This uses the fact that when you concatenate strings using the + sign you
get null if one of the strings is null

Null + "This" is Null
Null & "This" is "This"

If the above doesn't work then Address2 is not null, but probably contains
spaces or a zero-length string. If that is the case, you will need to use
an IIF statement.

.... & Address2 & IIF(Len(Trim(Address2 & "")) = 0, "", Chr(13) & Chr(10)) &
City & ...

 

[fredg]

Using this formula in a report:
=([FirstName] & " " & [LastName]) & Chr(13) & Chr(10) & [Address] & Chr(13)
& Chr(10) & [Address2] & Chr(13) & Chr(10) & [City] & "," & [State] & " " &
[zipcode]

If there is no address2 I get a blank line. Anyone know how I can get rid of
the blank line? Already have it marked yes to expand and shrink. Thanks

Try it this way:

= [First Name] & " " & [LastName] & Chr(13) & Chr(10) & [Address] &
Chr(13) & Chr(10) & ([Address2] + (Chr(13) & Chr(10))) & [City] & ","
& [State] & " " & [zipcode]

 

[DSmith]

Thanks, guys! Using the IIF statement made it work.

Try the following

=([FirstName] & " " & [LastName]) & Chr(13) & Chr(10) &
[Address] & Chr(13) &
Chr(10) & ([Address2] + Chr(13) + Chr(10)) &
[City] & "," & [State] & " " & [zipcode]

This uses the fact that when you concatenate strings using the + sign you
get null if one of the strings is null

Null + "This" is Null
Null & "This" is "This"

If the above doesn't work then Address2 is not null, but probably contains
spaces or a zero-length string. If that is the case, you will need to use
an IIF statement.

... & Address2 & IIF(Len(Trim(Address2 & "")) = 0, "", Chr(13) & Chr(10))
& City & ...

Using this formula in a report:
=([FirstName] & " " & [LastName]) & Chr(13) & Chr(10) & [Address] &
Chr(13) & Chr(10) & [Address2] & Chr(13) & Chr(10) & [City] & "," &
[State] & " " & [zipcode]

If there is no address2 I get a blank line. Anyone know how I can get rid
of the blank line? Already have it marked yes to expand and shrink.
Thanks