Hello all

I have a report set up to print out a sheet of address labels. It takes the various address fields into text boxes and then order them thus :

Name
Address1
Address2
Address3
Address4

I'm required to format the report to print out to a specific label sheet (3 x 6 on an A4 sheet). However, some address fields contain so many characters that the text wraps and over-writes the field below. I could increase the height of the text boxes, but then this would leave a gap when the field size did not take up two lines. How can I set the text box height to respond to the number of characters contained in the field?

Dave

Try using "Can grow" and "Can shrink" in textbox properties.

Yeah, that works for individual text boxes, but causes a text box to over-write the box below it. I want to extend them as a group. Apparently this has been possible in the past

Hi

When I am printing an address I always try to use just 1 text field, so any address lines that are not used don't leave gaps in the finished label. This is especially likely if you have fields called town, county, postcode etc.

I use the iif statement to get what I want.

In the record source of the single field I would put the following:

=[name] & Chr(13) & Chr(10) & [Address1] & IIf(IsNull([Address2]),"",[Address2]& Chr(13) & Chr(10)) & IIf(IsNull([town]),"",[town] & Chr(13) & Chr(10)) & IIf(IsNull([county]),"",[county] & Chr(13) & Chr(10)) & IIf(IsNull([postcode]),"",[postcode])

the chr(13) and chr(10) are carrage return line feed so you get a new line between each line of the address (I got this from the forum a while ago)

what the above gives you is:

name, new line
address1, newline (I assume the address always has at least one line)
if address2 has a value address2 and newline but if it is empty then nothing
repeat for all the possible address fields.

It is a bit fiddly, but once you get the method it is quite straightforward.

In your case I would use the above method and set the text box to the actual size of the label I wanted, setting the cangrow property to no as the box is already as big as it can be.

Hope this helps and doesn't put you off completely.

PS I have found that you can't take one of the text boxes you already have on the form and change it's datasource to a calculated datasource, it always gives error as the result, but copy the same expression to a new text box and it works.
This is probably a feature!

Sue.

That's an interesting solution.

Problem though - the characters Chr(10) and Chr(13) appear as

ĕĕ

rather than space and carriage return.

I assume I'm missing something?

Dave

Which version of Access are you using?

I have been using 2000 and XP and the characters seems to work with them.

When I do this in access 97 I just put a carriage return in the expression and it works OK.
If you are using 97 you may need to put shift return to put a new line in the text box rather than move to the field.

Hope this helps

Sue