Wish to create a label for an address using name, address1, address2, address3, city, zip,
country.
I want each address line to fall on a different line without blank lines
e.g. name='Ezra Marshall', address1= '#7 E49 St', address2= 'Brooklyn', address3= '',
city= 'New York', zip = '11234', country= 'USA' to
print as

Ezra Marshall
#7 E49 St
Brooklyn
New York 11234
USA

Ezra


You need to use IFs. Think how you would write it in straight code and then translate to sql. I'm not going to try and write it all because it will be horrible and I'll miss the odd bracket or comma but to give you an idea

IF(address1 IS NOT NULL,address1,'') +
IF(address2 IS NOT NULL AND address1 IS NOT NULL,#13#10,'') +
IF(address2 IS NOT NULL,address2,'') +
IF(address3 IS NOT NULL AND (address2 IS NOT NULL OR address1 IS NOT NULL),#13#10,'') +
IF(address3 IS NOT NULL,address3,'') +

etc etc

Roy Lambert

If the query is being fed to a report writer you may find it has a
setting to ignore null values in a case like this.

Using Fast Report 3 I don't have to worry about null address label
lines.