Removing blank lines from report - NOT using CanShrink 3

[jimbley]

I am currently developing a database application that stores customers' names and addresses. I have created three fields for each line of the address and three more for the town/city/postcode info. Not many people use the second or third address fields, so I would like to suppress the resulting blank lines that occur in reports. I have tried using the 'CanShrink' property, but it only works under certain conditions. Surely it would be possible create a larger text box and then combine all three fields via an expression that would automatically remove the blank lines? There must be a neater way round my problem than simply reformatting the report. Any help is greatly appreciated.

 

[scriverb]

I have addressed(no pun intended) this problem by doing the following: I create a control in the Report for the Name and size it accordingly. The next three controls are for the AddressLine1, AddressLine2, AddressLine3. I am assuming you are lining these up vertically like a mailing label arrangement. I create them the same width as the Name control but I make their height property set to 0. They will look like a thin line each. Now also set their Can Grow property to Yes. Select and place the three of them right on top of each other and right directly under the bottom edge of the Name control. You won't even be able to see them. Now put the other controls below all of those but not on top of the three thin lines. These should have regular heights as this information is always going to be available for display.

Now when the report prints it prints the Name control and then allows the others to grow only if there is data in them. with the City, St, Postal Info tucked right up under whatever has been printed from the Name, Address1, Address2, and Address3 controls. If ther is no data in any of the Address lines then that line will stay thin and not even show in your report.

Just a little trick to fool an otherwise pretty smart ACCESS application. Bob Scriver

 

[AlexMidd]

Brilliant solution! So simple and yet I would never have thought of that. Have a star! Have fun! )

Alex Middleton

 

[CosmoKramer]

Another, simpler way is to mimic what the label wizard does when creating a report. Set the Can Shrink property to Yes for each field that should be suppressed if null.

Giddyup!

 

[CosmoKramer]

jimbly,

Sorry, I didn't pay attention to your title that says you can't use Can Shrink.

Why is that? Is it because some fields may contain blanks instead of nulls?

If so, try this:

Add an unbound text box to your report (txtAddress) and set it's Can Grow property to Yes.

In the On Format event of the report's detail section, put code like this:

Me.txtAddress = IIf(IsNull([AddressLine1] Or [AddressLine1] = " "), "", [AddressLine1] & vbCrLf) & IIf(IsNull([AddressLine2] Or [AddressLine2] = " "), "", [AddressLine2] & vbCrLf) & IIf(IsNull([AddressLine3] Or [AddressLine3] = " "), "", [AddressLine3])

 

[scriverb]

CosmoKramer: I read your post yesterday and was in a real hurry. But, I had to come back and give you a star. I liked your idea also. Using a similar concept as mine but with a new twist of only using one text box and populating it with possibily three lines of addresses was great. Will put that one in my bag of tricks for later.

Thanks for the idea. Bob Scriver

 

[linzo]

To remove the blank lines when there is no data in the text box use the TRIM function in conjunction with the Can Shrink property.
In the control source section of the properties box add the following code: =TRIM ([tablename.fieldname])
Make sure there are no other controls adjacent to it on the same line which cannot shrink.
Linzo

 

[jimbley]

Linzo has demonstrated my aversion to using CanShrink/CanGrow. I want other data to appear alongside the address lines, but their size will be affected if I use either of these properties. I think CosmoKramer's method will be ideal if I just take the idea a little bit further. Perhaps a single text box consisting of the whole address would make things easier. Thanks for your help everyone, hopefuly I will be able to figure things out, but suggestions are always welcome. Keep up the good work.

Jim Lepp