Annoying spaces on address labels

[Phil Walker]

I've set up an Access database of a disabled friends
Christmas card address list and output this to 2x8 labels
with no problems, blank address lines don't appear as
blanks. However this year she is now unable to write all
the greetings so I've scanned four of the most common and
added the relevant jpg to the entry in the database. I've
created a custom label as big as the two pervious labels
on each row so that the report now prints the name and
address on the left hand label on each row and the
greeting jpg on the right hand one. As a result of this
the trim function does not seem to work now that a picture
is involved in the report as blank address lines are blank
in the report which looks messy. Is this a bug or am I
doing something stupid? I fear the latter!
Any help would be much appreciated.

 

[John Spencer (MVP)]

The CanShrink property works only as long as there is no other control in the
same horizontal space. So when you added the JPEG you ran into the problem.

You can change the way the address is built by concatenating the values of the
various fields all into one control or concatenating the field values in the
underlying query.

Add a new text control to your address side
Name it "txtFullAddress"
Set its control source to something like:

= FirstName & " " & LastName & chr(13) & Chr(10) &
AddressLine1 & Chr(13) & Chr(10) &
(AddressLine2 + Chr(13) + Chr(10) &
CityName & ", " & StateName & " " & ZipCode

The above all goes in one line, but for ease of reading I've formatted it into
multiple lines. What this does is use the plus sign to drop the carriage return
- chr(13) - and the line feed - chr(10) - if AddressLine2 is NULL.

You will need to set the other address controls to visible property to no to
make this work.

You can use the same technique in the query to build a calculated address and
then bind that field to ONE address control on the report and get rid of all the others.

 

[Fons Ponsioen]

From your description I deduct that the text blocks are
side by side witht the JPG block. This could put several
fields vertical besides the jpg field:
line 1 -----------
line 2 | jpg |
line 3 | |
line 4 -----------
even with the shrink properties set to eliminate blank
lines on the left, if the jpg has data in it the lines can
not be eliminated since every thing on one line has to be
blank in order for the shrink property to take effect.
What you could do is replace the line 2, 3, 4 with the
following IIF statements (make sure to rename the text
boxes so they are not the variable name;
in line 2
=IIF([line2]is null,IIF([line3] is null,[line4],[line3]),
[line2])
in line 3
=IIF([line3]is null or [line2] is null,IIF([line2] is null
and [line3] is null,"",[line3]))
in line 4
=IIF([line2] is null or [line3] is null,"",[Line4])
I hope i kept this straight in my mind.
Hope this helps.
Fons

 

[Marshall Barton]

I've set up an Access database of a disabled friends
Christmas card address list and output this to 2x8 labels
with no problems, blank address lines don't appear as
blanks. However this year she is now unable to write all
the greetings so I've scanned four of the most common and
added the relevant jpg to the entry in the database. I've
created a custom label as big as the two pervious labels
on each row so that the report now prints the name and
address on the left hand label on each row and the
greeting jpg on the right hand one. As a result of this
the trim function does not seem to work now that a picture
is involved in the report as blank address lines are blank
in the report which looks messy.

Nothing to do with the Trim function. The problem is that
CanShrink can not move the lower controls up when there is
something next to them that can't move up.

A different approach to using separate text boxes with
CanShrink for the addreass data is to use a single text box
with an expression that concatenates all the pieces together
in such a way to omit the blank parts. Here's an example
expression:

=[namefield] & (Chr(13) + Chr(10) + [address1]) & (Chr(13) +
Chr(10) + [address2]) & Chr(13) & Chr(10) & [city] & [state]
& [zip]

If either address1 or address2 is Null, the + will suppress
its associated new line.