By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,790 Members | 1,409 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,790 IT Pros & Developers. It's quick & easy.

How to eliminate horizontal gaps from blank data on report?

P: n/a
FZ
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!!!
Nov 12 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
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
'-------------------------------

Nov 12 '05 #2

P: n/a

Another way would be to place the fields on the report one beneath the
other, set their Height property to 0, CanGrow property to Yes, select
all of them and align top.

Unfortunately, can't say the same thing about labels. You'll have to
keep only one.

I've been using John's solution mostly in forms and this method in
reports, as it doesn't need an extra calculated field.

Good luck,

Dan
--
Posted via http://dbforums.com
Nov 12 '05 #3

P: n/a
Another option for labels would be to use unbound controls and keep up with
which was last used. Populate the next available control and count lines
printed. Force blanks to complex the existing label.
Nov 12 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.