fixing Mailing Label height w/ many fields & 'can shrink'

[dajabo]

I am trying to print mailing labels on 38x63mm labels.

My problem is that I have 11 potential fields (on seperate lines) to include, but any single address is never more than 7 lines high. Access increases the size of labels when you add extra text boxes, even if these text boxes are set to 'can shrink'.

I want to keep the label size constant but fill them with potential text from more fields than will fit on the actual label in design mode.

Is this clear? Let me illustrate:


Some addresses will use these fields only:

Name---------Joe Bloggs
2ndName
Position-------Administrator
Institution1
Institution2
Address1------35 Home Street
Address2
Address3
Town---------London
County
Postcode-----E3 8YY
Country

But some will include very different fields:

Name---------Harry Blink
2ndName-----Terry Album
Position
Institution1---The Library
Institution2---Acme Inc
Address1------1 Black Street
Address2
Address3
Town
County--------Brighton
Postcode------BB5 7UU
Country


When I include all these fields in text boxes in design mode, the label height increases from 38mm to about 50mm, thus misaligning my printing. All the text boxes are set to 'can shrink' so the labels preview fine and in the right format, but I want to keep the size of the labels the same, ie formatted to print 7 or 8 lines, rather than 11.

I can't include several fields on each line because there is no combination that works for every address, if you see what I mean.

There must be an easy way to do this, yes?

 

[reclusivemonkey]

Can't you create a grouping and set that to a specific height, then simply add all your text fields making sure they fit, and then add "CanGrow" and "CanShrink" to them all. If they won't all fit in your group (but you know that once the data is populated and blank field will shrink) try setting the height of all your text boxes to the smallest you can. I had a similar address label report to you but mine fit on the grouping space easily. One other way would be to 'fix' your address in a query so that took out any extraneous fields you didn't need (i.e. use [Address1]+Chr(13)+[Address2]) to get one large address field. You should be able to get just what you want with a little tweaking.

 

[dajabo]

Hi Thanks for your reply.

The last option sounds like it could be best for this. But I'm still learning about queries.

Do you mean I could set up a query that manipulates each address individually and removes empty fields etc, and then which can be called by the mailing label report and set into a pre-defined space, like 7 lines?

Um, if so how do I go about this?

 

[trucktime]

Label problem

You only want to print a field when it has a value,
if the field is blank, it needs to be skipped.

You can try the following:
In the Control Source of the field put this code:

=IIf(IsNull([Address2]),"",[Address2])

If field Address2 has a value, it will print, if not, it skips.

See if this will work for you.

 

[dajabo]

Hi Trucktime, I know how to avoid printing null fields ... I am using the 'can shrink' option in text box properties in the report.

My problem is that the number of fields in which potential data may be increases the label size in design mode. I have 38mm high labels and including 11 lines of text boxes increases the size of the label to 50mm high. I could make each text box smaller but this isn't ideal for me.

I liked the previous contributors idea about a query that may allow me to include just one or two lines of command in the report in order to call a previously formatted or selected address obtained through a query. I think that could work.

 

[reclusivemonkey]

Hi Dajabo,

I've had a quick look at the query I was suggesting, but it seems it doesn't give quite the results I was expecting! I really do think that the only way to do this is to set your fields in the report to a very small height so that they fir on your page. Then set all the text fields to "CanGrow" and this should work. If you want to try with a query, try creating a field which has something like [Address1] & char(13) & chr(10) & [Address2]. However, doing this will add line breaks wherever, so you really need to filter the fields which won't be included. Sorry I can't be of any more help, if you really do get stuck, send me PM I'll see what I can do.