Address Labels

[Basia]

I have a table that has fields Address, Address2 and Address3. Often the Address 3 field is null. How do I get the labels to remove the field when it is null? It currently leaves a space on the label.

I have tried:

=trim[(address)&"
"&(address2)&"
"&(address3)]

and

=trim[address]
=trim[address2]
=trim[address3]

Any help out there?????

Thanks,
Basia

 

[WildHare]

How do I get the labels to remove the field when it is null? It currently leaves a space on the label.

I have a question for you. If there is no data, what WOULD you like to see on the label?

You probably need a substitution algorithm something like:

Line1 = Name
Line2 = Street
Line3 = Address1
Line4 = IIF(Address2, Address2, City)
Line5 = IIF(Address2, City, State & Zip)

If you have more address lines, just alter for your situation.


Ex-JimAtTheFAA
78.5% of all statistics are made up on the spot.
Another free Access forum:

http://www.forumco.com/jmhare

http://www.jmhare.com

 

[Basia]

Thanks for the quick feedback. If I have no Address 2 or Address3, how would I do this? Do I need to imbed another iif?

Line1 = Name
Line2 = Street
Line3 = Address1
Line4 = IIF(Address2, Address2, City)
Line5 = IIF(Address2, City, State & Zip)

Basia

 

[WildHare]

You just have to break it down and allow for them. Assuming you have Name, Address1, Address2, Address3, City, State and Zip fields, and you want your 'label block' to just have the appropriate lines, here's how I'd do it (Assumes you have a NAME and and ADDRESS1 and CITY, STATE and ZIP all the time - but if you have NO address2, would you ever have an ADDRESS3? That's where it get's sticky.

Line1 - Name
Line2 = ADDRESS1
Line3 = IIF(Address2, ADDRESS2, City)
Line4 = IIF(Address3, ADDRESS3, (IIF(ADDRESS2, City, STATE & ZIP)))
Line5 = IIF(Address3, CITY, (IIF(ADDRESS2, STATE&ZIP, "&quot))
Line6 = IIF(Address3, State & ZIp, " &quot


Starting with Line3, if there is an ADDRESS2, use it, otherwise plop the CITY there.

On Line 4, if there's an address 3, use it, (assuming Address2 is on line 3) otw if there's an ADDRESS2, we want to use the CITY, and if no 3 or 2, use STATE & ZIP. I think. (whew!)

On line 5 if there's an address3,(who would HAVE to be on line 4), put the CITY, otw If there's an ADDY2, we want the STATE and ZIP, and if NO LINE 2, we want a blank)

On line 6, if there's an address3, then City has to be on LINE 5, so line 6 has to have State & ZIP. IF there is No ADDRESS3, then STATE & ZIP are on line 5, so Line 6 is BLANK.

Got it? (I think I'm dizzy here...whooooooo)



Ex-JimAtTheFAA
78.5% of all statistics are made up on the spot.
Another free Access forum:

http://www.forumco.com/jmhare

http://www.jmhare.com