Hide/collapse space for empty fields in report 11

[KarlGB]

Hey Folks,

I will be eternally grateful to anyone with tips on this question:...

I'm trying to create a simple report pulling in fields from one table and no fancy filtering. However, I want the report to hide fields and field labels (and collapse that space) that DO NOT contain any information (empty fields).

I assume there is a way to do this by basing the report off of a query... but haven't a clue as to the criteria I would use..

Any advice anyone!? Thanks ahead of time!!

Karl

 

[rickj65]

You'll probably have to provide a bit more detail in order to get more meaningful input, but I'll try.

Can you be more specific in describiing a particular record that contains the problem you want to solve?

Also, what kind of layout does the report currently have? If the report is columnar (field labels serve as titles above each column and the report produces continuous rows), then any physical changes you make to a particular field (e.g. hiding, highlighting, etc.) will affect the display of that field for all rows, regardless if they should be hidden or displayed.

If the report you have is more of a one-page report like a form-letter, then you can use VBA code in the OnFormat event of that particular section. In the OnFormat event, you'd put something like:

If IsNull(MyField) Then
MyField.Visible = False
MyLabel.Visible = False
Else
MyField.Visible = True
MyField.Visible = True
End If

However, when the report prints out, it will leave a space where the field was and will not "collapse" anything, although I'm not sure exactly what you mean by collapse.

How/where/why are your field labels set up so that you'd want to hide the label if the field is Null?

I don't think I've answered your question, but maybe I've provided you feedback to better answer your question.

 

[lezleew]

Karl -
I have this same question. Did you ever get it answered?

LezLee

 

[scriverb]

I think what you are referring to is a situation like an address label. Name, Address line1, Address Line2, City state zip. They are all stacked on top each other and when there is blank data the space is left there making it look really weird. Here is how you fix this problem.

Create your bound controls and stack them as normally so that when they have data they will look appropriate. Now select them all and change the Height property to zero(0). They now look like a horizontal thin line. Change the Can Grow property of all of them to Yes. While they are all highlighted select from the Format menu Vertical Space - Decrease. Keep doing this until all the lines move up and push right up underneath the top line. They should all look like a single thin line.

They will now only display data when it is there because of the Can Grow property setting. If let's say the second Address line is empty it will not expand and take up dead space.

I hope this is what you were looking for.

Bob Scriver

Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[jerseyboy]

I love a clever solution! This really works well!
Enjoy your star, Thanks!

JerseyBoy
Remember: self-praise is no recommendation

 

[scriverb]

Thank you JerseyBoy. I do appreciate it. I have been using this technique for a while after I was printing lables that had two lines of the address line and it looked really weird with the blank line.

Enjoy.

Bob Scriver

Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[dcurtis]

Another good solution from Bob. Here's a star from me too, because I will use it.

 

[hansproperty]

Hi Bob
I too have had this problem and solved it in the way that you outlined, however, my report contains two columns, and when the address fiels grow, the second column also grows with a large and ugly gap. Any solutions?

Regards
Hans

 

[hansproperty]

Hi Bob
I too have had this problem and solved it in the way that you outlined, however, my report contains two columns, and when the address fields grow, the second column also grows with a large and ugly gap. Any solutions?

Regards
Hans

 

[scriverb]

I don't think so. The Growing is all the way horizontally across the report. I haven't found a way to solve this problem other than increasing the height property of the growing control. This would require a considerable amount of code to figure the number of characters that can be printed across the control so you would know how tall to make the control. You would have to use a font that has equal widths for all characters so that you can have an idea of width per inch.

Just an idea. Let me know if you try that one.

Bob Scriver

Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[RickSpr]

Instead of using CanGrow, I leave the text boxes at their original size and set the CanShrink property to Yes. That way they're still readable in Design View.

As with CanGrow, the shrinking only occurs if the space is empty all the way across the page.

Bob, is there something you know that I don't about how CanGrow works better than CanShrink for this purpose?

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[scriverb]

With the CanShrink property you are allocating the space already and if no data is available it shrinks to nothing. But, the control below that one has it's own place on the report and will not move. So, a blank space.

With the CanGrow and all the controls shoved up tight only the controls that have data in them will move down and display the data. Thus, out of 4 controls if 1,2, and 4 have data they will be placed in 1,2,3 locations.

Does this make sense to you.

Bob Scriver

Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[RickSpr]

Bob,

I understand what you're saying, but it doesn't match my experience. I find that if control 3 is empty and collapses, the entire horizontal band it occupies is squeezed out, and control 4 moves up into its place.

Perhaps it's because I always set the report section's (usually Detail section's) CanShrink property as well. Or perhaps I'm just misremembering. I'll check it out and post back tonight.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[RickSpr]

I have verified that CanShrink controls on a CanShrink report section will compress vertically, with subsequent controls moving up into their space.

In other words, instead of making your controls zero height and jamming them up against each other, leaving it hard to identify or select a control by clicking on it, you can leave them at their design size and set their CanShrink properties to Yes, along with the form section's own CanShrink property.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[scriverb]

Rick, you are correct. I stand corrected on that. It was a technique that I have used for a long time and just never tried the Can Shrink technique. I falsly assumed that the shrinkage of the control would have no effect on other controls below it.

Will use it in the future.

Bob Scriver

Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[scriverb]

Sorry, Star for you. Forgot to post that.

Bob Scriver

Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[RickSpr]

Hey, thanks, Bob. It's an honor, especially coming from you.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[Tekime]

Wow, I was about to ask this very question, when to my delight the answer is right here Rick your solution is perfect for vertical collapsing. I might not be a Bob, but you certainly deserve a star for that one!

I am still having trouble with horizontal fields, is there a way to collapse fields to the length of the value? For example, I would like to display the full name of a customer in a report and my fields look like this:

[title] [firstName] [middleInitial] [lastName]

But the output would look something like this:

Mr. JOHN J JONES

Not very professional, and these reports need to look nice.

I took both of the methods described above and applied them horizontally, to no avail. When they grow they insist on growing vertically!

TIA :O)

http://www.fury-tech.com

 

[CosmoKramer]

hansproperty,

I usually handle this situation differently. This method will handle the side-by-side column problem.

Add an unbound text box to your detail section to hold the formatted fields (for example, txtCustAddress). Set its Can Grow and Can Shrink properties to Yes.

In the On Format event of your detail section, put code like this to populate that text box you just added, of course changing to your field names:

Me.txtCustAddress = IIf(IsNull(Me.[Customer add 1]), "", Me.[Customer add 1] & vbCrLf) & IIf(IsNull(Me.[Customer add 2]), "", Me.[Customer add 2] & vbCrLf) & IIf(IsNull(Me.[Customer add 3]), "", Me.[Customer add 3] & vbCrLf) & IIf(IsNull(Me.[Customer add 4]), "", Me.[Customer add 4] & vbCrLf) & IIf(IsNull(Me.[Customer add 5]), "", Me.[Customer add 5])

Code like this will handle multiple column reports as well as side-by-side text boxes.

Let me know if this helps.....

 

[RickSpr]

Tekime,

What I do for horizontal squeezing is simply use a concatenation expression as the Control Source for a calculated control. In this case, I'd use:
=[title] & " " & [firstName] & " " & [middleInitial] & " " & [lastName]

If a field might be Null, and you don't want to leave an extra space, you have to modify the terms slightly. For example, if Title and/or middleInitial might be Null, use:
=IIf(IsNull([title]), "", [title] & " &quot & [firstName] & " " & IIf(IsNull([middleInitial]), "", [middleInitial] & " &quot & [lastName]

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein