Strategy for addresses with multipe multiple fields (1 Viewer)

I generate reports that are used for billing. As a part of that the reports will have the billing persons name, title, address, address2, city_state_ZIP, telephone, cell and email. In the billing persons form that contains that information, the fields used vary by person. By that I mean not everyone has a address2, (Apt B), some people don't have a telephone number but they have a cell, some don't have telephone or cell but they have an email address.

To format this correctly, you want something like:

John Smith
Owner
123 Oak St.
Apt B
Anywhere, City 12345
(XXX) XXX-XXXX
(YYY) YYY-YYYY
johnsmith@whoknowswhere.com

If anyone of the above isn't in the billing persons profile, everything moves up. No spaces between lines. I typically do this with a series of If-Then-Else statements but honestly it becomes a pain in the butt as you move your way down the above lines.

Does anyone have a better way to handle this?

Thanks in advance,

Chuck

I often use the fact that + propagates Nulls while & does not. Not at a computer, but maybe that points you in the right direction? If not I'll be on a computer later.

yes the +
and use only 1 textbox, the recordsource:

=([name] + vbcrlf) & _
([title] + vbcrlf) & _
([address] + vbcrlf) & _
([city_state_zip + vbcrlf) & _
([telephone + vbcrlf) & _
([cell] + vbcrlf) & _

Arnelgp,

That was the code I needed, it worked with a few modifications. Here is the actual code that worked in the report form TEXT box:

=([rTitle]+Chr(13)+Chr(10)) &
([LAddress]+Chr(13)+Chr(10)) &
([LAddress2]+Chr(13)+Chr(10)) &
([LCity] & ", " & [LState] & " " & [LZIP]+Chr(13)+Chr(10)) &
(IIf([LTel] Is Not Null,"Tel: " &
Format([LTel],"(000) 000-0000"))+Chr(13)+Chr(10)) &
(IIf([LCell] Is Not Null, "Cell: " & Format([LCell],"(000) 000-0000"))+Chr(13)+Chr(10)) &
[LEmail]

Two notes: 1) in a text box I received an error message if I used the underscore character as you showed in your reply so I eliminated that and 2) the report Text box doesn't know what vbcrlf is, but it knows what Chr(13) + Chr(10) means.


This is a whole lot easier than nested if-then statements. Works great and makes this a whole bunch easier.

Thank you again!

Chuck