printing new line on a Access 97 report (Access 97)

Topic

New Reply

Hello:

I need to create a mailing lables report with 3 or 4 lines. The database has a address line 2 field incase there’s an apartment or the address is to long to fit on one line.

Very few of the records use this field so I’ve declared the control source for address2 as follows:
=IIf(IsNull([address2]),[City] & “, ” & [Province] & ” ” & [PostalCode],[address2] & chr$(13) & [City] & “, ” & [Province] & ” ” & [PostalCode])

in otherwords, if address 2 is null, as it usually is, then print city, province and postal code.
If address 2 is not null then print
address 2 line feed and city ….

When I run the report, everything works except the new line. The address 2 and city province … are on the same line.

Any ideas?
Thanks.

Reply | Quote

Replies

Is there a reason you are not using the Label Wizard? I ask because if you create a new report and choose the Label Wizard in the New Report Window, it will handle this for you. It’s easy and it always works for me.

Reply | Quote

— I don’t know if this will be helpful or not, but I always use the character combination of chr(13) and chr(10). One is the line break, and one is the carriage return, I believe. — Pat

Reply | Quote

There’s a simpler way to handle this. Put all the fields on your report as if each one was fully populated. Then take your Address2 and make it as close to zero height as you can get it, but set its cangrow and canshrink properties to true. Then snug the next line of controls for city, etc. up right underneath Address2. What will happen is that if there is data in Address2, the line will expand to display the data and push the following lines down. Otherwise, Address2 will be just a thin line that won’t affect the appearance of the rest of the address.

Reply | Quote

Hi,

————-
Then take your Address2 and make it as close to zero height as you can get it, but set its cangrow and canshrink properties to true.
————-

I have a question to this, because it was repeatedly stated here. Is there any specific reason to make the field height so small by setting the canshrink property to true? I think, if the property is set, the field height will shrink automatically to 0 if there is no data in it – that’s what the property is for. Or am I missing something?

I always make the fields with normal height and it doesn’t seem to me that there is something wrong when there is no data in them. I would find it cumbersome to have such thin fields, because then it’s difficult to “read” the report in design mode and specially with labels or other limited formats I need to see the whole space needed by the data when layouting them.

Reply | Quote

Yes, you’re missing the fact that even if the control shrinks, the space it occupies doesn’t. So if you don’t make the control as short a height as you can, you’ll wind up with a blank line in the report when the control contains no data. This is a somewhat advanced report trick, and it’s up to you whether you use it or not and whether you get blank lines in your report or not. Noboby is trying to force you to use it, but it will accomplish what you’re trying to do and your method won’t.

If you make it very short and move the other controls up close to it, it will expand when it contains data and contract when it doesn’t. When it expands, it moves the controls below it down so you get that line with the others following it as you would wish. When it is null, it just stays shrunken and the other controls fill in normally without any blank line.

Oh, and labels do *not* grow and shrink. If you use labels, they’re always there full size even if the field they’re attached to shrinks.

Reply | Quote

Hi Charlotte,

what I meant is, setting the canshrink property to true should be enough. The Access help describes exactly the behaviour you wrote about – cangrow and canshrink are sort of twins, working symmetrically. But we all know the help isn’t 100% So I tested this in A2000 (I had the last “naked” report without labels, lines etc. in the detail section in a A95 database, converted in A97 and then modified). I set up a simple report of the address label kind:

firstname lastname
address
zip code
city

No labels, no lines, nothing else, all controls have “normal” height, and I set the canshrink property for all controls except the first/lastname to true. And it does work. I got no empty lines in the report. The only thing is, if there is some vertical space between the controls, they will be preserved and get added. If say the address line is skipped while empty, there would be a larger space between the names line and the zip code. But this is definitely not a blank line in the full height of the skipped control, it is exactly twice the vertical space between the controls.

And this is the same, whether its a single column or a multiple column report, or whether the detail section’s canshrink property is set to true or false. Only, if the latter, there will be a larger space between one address record as a whole and the other if there are empty controls in the first, which should be if printing address labels.

If one had to set the control height to 0 in order to skip blank lines, then the canshrink property would make no sense. The 0 height +cangrow property would do the job.

This whole thing concerns only bound controls having a table/query field as a source. If the control source is something else, then it’s an other story, but then one has to do some formatting per VBA anyway.

And BTW, I am very sorry for the confusion. With “labels” I meant address labels, not the label fields in the report. I’ll try to be more careful with my translations from german

Reply | Quote