Skiping Blank Lines in a Printed Report

[DevilsSlide]

I want to create a report that would skip (not print) blank lines. In effect and an Address Book Report with the following basic format:

[Company Name]
[IndivName]
[AddressLine1]
[AddressLine2]
[AddressLine3]
[City], [State] [Zip]

It is a relational database that I'm creating and based on the Selection Criteria the records may be of mixed type (Company Only, Individual(s) at Company and Individual Only) and may use 1, 2 or 3 Address Lines. What I'd like for the report to do is to "Skip Blank Lines." In an MS Word Mail Merge there is an Option to Skip Blank Lines, but I have not been able to figure out a way to do this in an Access Report. One Record has 9 total individuals in it, most only have 1 to 4.

I can live with not putting other info (IE: Phone Numbers - Business, Individual Phone and/or Extension and Fax# = That ALL may or may not be populated with more or less lines) to the right of the Mailing Address Info listed above. But to cut down the White/Blank Space on the report I'd also like to be able to include other information to the right, and would likely need to apply the same resolution as to those of the Address Info fields.

I might also have multiple Points of Contact with different phones & phone exten's that I'd like to keep grouped together. I'm thinking I'll need to do a Report(s) within a Report if I can do that???

Still my biggest obstacle is How-To Skip Blank Lines WITHOUT creating dozens of reports for each of the combinations above.

not being good with VBA I'd like to avoid it as much as I can.

Any thoughts? Any and All Help will be gladly accepted!

Thanks

John

 

[annsolomon]

Here's what I put in the Control Source of a text box on my work order form:

=IIf(IsNull([StreetAddress]),"",[StreetAddress] & vbcrlf & IIf(IsNull([City]),"",[City] & ", &quot & IIf(IsNull([State]),"",[State] & " &quot & IIf(IsNull([Zip]),"",[Zip])

This puts my city, state, and zip on one line but you could separate them with vbcrlf if you prefer. Ann

 

[danvlas]

Well, here's a nice trick:

Put all text boxes one below the other.
Set their CanGrow property to True
Set their Height to 0
Select them all
Format-Align-Top

and run the report...

Good luck

Daniel Vlas
Systems Consultant

 

[VicM]

John:

Set the Can Shrink property for each of the controls to Yes.

HTH,

Vic

 

[VicM]

John:

P.S. This will only work if there are no labels on the line for each field. If there are, then the label isn't blank and the row will still print.

Removing the label requires additional coding.

HTH,

Vic

 

[DevilsSlide]

Thank you One and All.

They all look like very good suggestions. It may be the weekend before I get to try them.

Ann - I was thinking that when I first started this project over three months ago and then got back to it this week that I'd used 3 Address lines, but I only set it up with 2. Would this work with 2 Address lines:

=IIf(IsNull([AddressLine2]),[AddressLine1],[AddressLine1] & vbcrlf & [AddressLine2])...

I guess that if I had used 3 lines I could create a nest within a nest.

I'd never seen or used 'Nz' until reading some of the posts last night - What would be Pros and Cons of using it here like:

=IIf(Nz([AddressLine2]),[AddressLine1],[AddressLine1] & vbcrlf & [AddressLine2])...

Again Thanks for the Help!

John

 

[annsolomon]

I use the Nz function when I'm working with numbers and want to provide a value to prevent a calculation from bombing out in the event of a null value.

When using Is Null you need to provide two parameters: The first parameter says what you want to do if the field is null and the second parameter says what you want to do if the field is not null.

In my example, if I don't have a street address I direct a null "" value and do not force a line feed; rather I print the second line of the address in its place. If I do have a street address, I print it then force a line feed then print the second line address.

So, how you use it basically depends on what data you can count on/how you want to handle it. Ann