FZ wrote:
Hi gang,
I was wondering if someone would be kind enough to help me with the
following problem. I have created a report that prints shipping
labels. The problem is that I have some customers that have
multi-line addresses and some that don't. So, say my report layout
looks like this:
Name
Address1
Address2
Address3
City State Zip Code
Now, if the customer has 3 lines of address, everything's great, it
looks fine. But, if the customer only has 1 line of address (so
Address2 and Address3 are null), it comes out looking like this:
Jim Jones
123 Main Street
Anytown, Anystate 99999
So my question is, how can I design this so that the report will
eliminate those horizontal lines that have no data? Is there a way to
do this?
Thanks for any guidance!!!
The best way (IMO) is to use the Null propogation method in your query. If you
concantenate values using the '+' concatenation operator it propogates nulls.
That is
"123 Main" & (Chr(13) + Chr(10) + "Apt. 1")
will result in
123 Main
Apt. 1
but if the second address part is Null then you would only get one line
123 Main
becuase the '+' would make the entire expression within parens as Null because
one part was Null (where the second address part was Null)
Thus for your query you could use:
Addr: Name & Chr(13) & Chr(10) & AddressLine1 & (Chr(13) + Chr(10) + AddressLine2)
& (Chr(13) + Chr(10) + AddressLine3) & Chr(13) & Chr(10) & City & ", "
& State & " " & Zip
Some tweaking may be needed but hopefully you get the idea.
The key is there is a difference in how the & and + concantenation operators work.
The immediate window (Ctrl-G) is your friend here for testing different
possibilities. Good luck.
--
'-------------------------------
' John Mishefske
'-------------------------------