Name & Address without blank lines

[NeilT123]

I currently use a text box with the following
=[Account Name] & Chr(13) & Chr(10) & [Address 1] & Chr(13) & Chr(10) & [Address 2] & Chr(13) & Chr(10) & [City] & Chr(13) & Chr(10) & [County] & Chr(13) & Chr(10) & [Post Code]

to produce a name and address label but if for example the field address 2 is empty the label has a blank line. How should I set this up so as not to have blank lines?

 

[PCLewis]

It's messy as an expression especially if some of the fields might contain nulls or empty strings.

=IIF(nz(trim([Account Name]),"")<>"",[Account Name] & Chr(13) & Chr(10)) 
& IIF(nz(trim([Address 1]),"") <> "",[Address 1] & Chr(13) & Chr(10)) 
& IIF(nz(trim([Address 2]),"") <> "",[Address 2] & Chr(13) & Chr(10)) 
& [Post Code]

Also you can replace CHR(13) & CHR(10) with vbCRLF

=IIF(nz(trim([Account Name]),"")<>"",[Account Name] & vbCrLf) 
& IIF(nz(trim([Address 1]),"")  <> "",[Address 1] & vbCrLf) 
& IIF(nz(trim([Address 2]),"")  <> "",[Address 2] & vbCrLf) 
& [Post Code]

 

[THWatson]

NeilT123
An alternate approach...

Assuming that the Account Name, Address 1, City, County and Post Code fields would always contain data, would it make sense to have 4 separate text boxes? This assumes also that you would need only 4 lines on your address label.

First text box, on line 1
=[Account Name]

Second text box, on line 2
=[Address 1]

Third text box, on line 3
=[Address 2]
Set the "Can Shrink" Properties for this text box to Yes. Then if there is no data in this line, it will go away.

Fourth text box, on line 4
=[City] & ", "& [County] & " " & [Post Code]


Tom

 

[NeilT123]

Thank you. I'll give both suggestions a try.

 

[dtay1132]

I had the same problem and the Nz function solved it. I just created a variable for my address2 value in the underlying query based on the Nz function

Address2line: Nz([Attendee_Addr2]," ").

this puts a space in the Address 2 line. You could close the double quotes and elminate the space all together.