Format an address

I need a little help in formatting the address field. We use a 3 or 4 line
address field at the top of a letter like this:
Name
Address 1
Address 2
CityStateZip
I want to put them all in one textbox but sometimes Address 2 is NULL and I
would like to supress the one line. The code looks like this:
=Fields!FullName.Value & vbcrlf & Fields!Line1.Value & vbcrlf &
Fields!Line2.Value & vbcrlf & Fields!CityStateZip.Value
Does anybody know how to supress the NULL line? Is there a better way to do
this?
--
ZipHow about something like
=iif(Fields!Line2.Value is Nothing,
Fields!FullName.Value & vbcrlf & Fields!Line1.Value & vbcrlf &
Fields!CityStateZip.Value,
Fields!FullName.Value & vbcrlf & Fields!Line1.Value & vbcrlf &
Fields!Line2.Value & vbcrlf & Fields!CityStateZip.Value )
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Zip" <Zip@.discussions.microsoft.com> wrote in message
news:AA45AB20-23F9-4449-94D9-863B963FABB4@.microsoft.com...
>I need a little help in formatting the address field. We use a 3 or 4 line
> address field at the top of a letter like this:
> Name
> Address 1
> Address 2
> CityStateZip
> I want to put them all in one textbox but sometimes Address 2 is NULL and
> I
> would like to supress the one line. The code looks like this:
> =Fields!FullName.Value & vbcrlf & Fields!Line1.Value & vbcrlf &
> Fields!Line2.Value & vbcrlf & Fields!CityStateZip.Value
> Does anybody know how to supress the NULL line? Is there a better way to
> do
> this?
> --
> Zip|||We use a lot of these too. I package the second Address field in an 'Iif'
statement like so, where if the Address2 field is NULL an empty string is
simply appended onto Address1, otherwise there is a line break and then the
Address2 Field:
Fields!Address1.Value &
Iif((IsNothing(Fields!Address2.Value, "", vbcrlf & Fields!Address2.Value)
& vbcrlf & Fields!CityStateZip.Value
There may be cleverer ways to do it but that works for me.
Hope that helps.
rgorslin
"Zip" wrote:
> I need a little help in formatting the address field. We use a 3 or 4 line
> address field at the top of a letter like this:
> Name
> Address 1
> Address 2
> CityStateZip
> I want to put them all in one textbox but sometimes Address 2 is NULL and I
> would like to supress the one line. The code looks like this:
> =Fields!FullName.Value & vbcrlf & Fields!Line1.Value & vbcrlf &
> Fields!Line2.Value & vbcrlf & Fields!CityStateZip.Value
> Does anybody know how to supress the NULL line? Is there a better way to do
> this?
> --
> Zip