Address Label Problem

D

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
 
J

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 & ...
 
F

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]
 
D

DSmith

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

John Spencer said:
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 & ...


DSmith said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Post Codes 2
Report control: hide blank row when null data 2
Address box with IIf Statement 7
Carriage Return 2
Carriage Return in calculated field 7
Not printing field if not there 2
Mailing Labels 3
Labels 4

Top