Empty lines in concatenated text box - help?

[Sue Compelling]

I have the following text box control source of:

=[IFirstName] & " " & [ILastName] & Chr(13) & Chr(10) & [IPAddress] &
Chr(13) & Chr(10) & [IPDistrict] & Chr(13) & Chr(10) & [IPCity] & " " &
[IPPostalCode]

though there are some instances where the record does not have the District
or City details and I don't want the result to have empty lines - eg:

David Kirton
33 Round Street

AUCKLAND 1007

Is there a way I can eliminate this from happening?

TIA --
Sue Compelling

 

[Wayne Morgan]

Yes, there is a way as long as what is being concatenated can NOT be
interpreted as a number by Access. This will only work with text (at least
as far as I've been able to tell). It takes advantage of the fact that Null
will propagate through an expression. To do this, change your Control Source
to:

=[IFirstName] & " " & [ILastName] & Chr(13) + Chr(10) + [IPAddress] &
Chr(13) + Chr(10) + [IPDistrict] & Chr(13) & Chr(10) & [IPCity] & " " &
[IPPostalCode]

I left the last one a & since IPPostalCode is on the same line as IPCity.
The way this works is, if IPAddress is Null, then since Null propagate
through an expression and we are now adding (+) instead of just
concatenating (&) then

Chr(13)+Chr(10)+[IPAddress]

will return Null.

 

['69 Camaro]

Hi, Sue.

Is there a way I can eliminate this from happening?

To avoid the extraneous carriage return, space, and problems with possibly
"adding" values instead of text strings, try:

=[IFirstName] & " " & [ILastName] & Chr(13) & Chr(10) & [IPAddress] &
IIF(Len([IPDistrict]) > 0, (Chr(13) & Chr(10) & [IPDistrict]), NULL) &
Chr(13) & Chr(10) & IIF(Len([IPCity]) > 0, ([IPCity]) & " ", NULL) &
[IPPostalCode]

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.

I have the following text box control source of:

=[IFirstName] & " " & [ILastName] & Chr(13) & Chr(10) & [IPAddress] &
Chr(13) & Chr(10) & [IPDistrict] & Chr(13) & Chr(10) & [IPCity] & " " &
[IPPostalCode]

though there are some instances where the record does not have the District
or City details and I don't want the result to have empty lines - eg:

David Kirton
33 Round Street

AUCKLAND 1007

Is there a way I can eliminate this from happening?

TIA --
Sue Compelling

 

[Sue Compelling]

Thanks Wayne [& + Gunny] - brilliant , looks much better. We've got
elections on in NZ tonight so I'm just getting in a bit of cerebral before
the games begin.
--
Sue Compelling

Yes, there is a way as long as what is being concatenated can NOT be
interpreted as a number by Access. This will only work with text (at least
as far as I've been able to tell). It takes advantage of the fact that Null
will propagate through an expression. To do this, change your Control Source
to:

=[IFirstName] & " " & [ILastName] & Chr(13) + Chr(10) + [IPAddress] &
Chr(13) + Chr(10) + [IPDistrict] & Chr(13) & Chr(10) & [IPCity] & " " &
[IPPostalCode]

I left the last one a & since IPPostalCode is on the same line as IPCity.
The way this works is, if IPAddress is Null, then since Null propagate
through an expression and we are now adding (+) instead of just
concatenating (&) then

Chr(13)+Chr(10)+[IPAddress]

will return Null.

--
Wayne Morgan
MS Access MVP

I have the following text box control source of:

=[IFirstName] & " " & [ILastName] & Chr(13) & Chr(10) & [IPAddress] &
Chr(13) & Chr(10) & [IPDistrict] & Chr(13) & Chr(10) & [IPCity] & " " &
[IPPostalCode]

though there are some instances where the record does not have the
District
or City details and I don't want the result to have empty lines - eg:

David Kirton
33 Round Street

AUCKLAND 1007

Is there a way I can eliminate this from happening?

TIA --
Sue Compelling