473,320 Members | 2,104 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

How to eliminate horizontal gaps from blank data on report?

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
3 2868
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

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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

6
by: christopher.secord | last post by:
I have a table containing typed log entries. One log entry is supposed to be created every twelve hours, but sometimes there are gaps. I need to create a report showing the time of entry, and the...
5
by: cool2005 | last post by:
I have a table contains 4 cells (2 x 2) like this http://www.coolshare.com/planttrade/temp/gap.jpg each cell contains a div and the div contains a table. As you can see from the image above,...
5
by: MX1 | last post by:
Hi, I have a report with Name1 and Name2 in the address section . Sometimes, Name2 is not populated with data via the query that feeds the report. Unfortunately, the blank line stays in the...
4
by: Mark Reed | last post by:
Hi Guru's, Hopefully I can explain this OK.....What I am trying to do is create a QC check sheet using the following fields: ASN, PO & Qty. This information will be at the top of each report with...
7
by: Richard Yardley | last post by:
I have a mailing label report and would like to remove the space taken up by rows with no info (move the other rows up). I am a beginner using MS Access 2000 and would appreciate any help. ...
6
by: noway | last post by:
I have greated a report and have included a page break in it. The report breaks were it is supposed to but then it creates a blank page between the two pages. Since this report will print out...
5
by: Arno R | last post by:
I need to find the 'gaps' in a numbered recordset like: 1 Philips 2 Jones 5 Jo Do 6 Frenzy 10 John Matthias 23 Arno R (and so on ....) What I need is one...
6
by: briggal1 | last post by:
Hi I wonder if anyone can help. I have a report in Access 2000, which works perfectly and displays all the information until I select a period where one, or more, of the fields is blank. ...
2
by: Eliza | last post by:
we have written a C# program which generates an XML file from a Client computer and this file is being transfered to the Server side(using Socket programming). at the Server side we are first...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.