Hide/ shrink space for empty fields

[ncook]

I have been working on a report which contains a lot of fields. But many fields do not contain any data, and i want to remove the space. I have tried many methods but i NEED to keep the bound labels in line when the fields do shrink. How can i do this?
Please, Please Please Help!!

 

[ColinEssex]

Presumably you mean that the fields in question have data in some records but not in others? or are the fields empty for all records?

Col

 

[Rich]

Set the CanShrink property to yes

 

[ncook]

There is data in some fields, and no data in others.
And when i set the can Shrink to Yes, it removes the data, but keeps the labels, so i still have a blank space.
I want to be able to keep the label and the text box when there is data in that field, and i want the label to disapear when there is no data in the text box, so nothing relating to that field is displayed. i hope this makes sense!

 

[Pat Hartman]

You'll need code in the Format event of the Detail section. Check each relevant field for null. If it is null, hide the label.

If IsNull(Me.YourField) Then
Me.YourField_lbl.Visible = False
Else
Me.YourField_lbl.Visible = True
End If

 

[ncook]

Thankyou for your help, I just have one further request.
The IsNull event works, it removes the label and by using the Can Grow/CanShrink function this removes the text box, but I still get a huge blank space where the null values were.

If i make the size of the text box to 0 and then decrease the vertical spacing it leaves me with no blank spaces, but all the labels overlap and they are not alongside the text box that they relate to.
How can i now get the correct label alongside the correct textbox without having a blank space where the Null values were?

 

[Pat Hartman]

CanShrink will only work if the controls do NOT overlap so make sure that no control overlaps another. The other thing is to make sure that the CanShrink/CanGrow properties of the SECTION are also set to yes.

 

[jdgnick]

I have searched the forums and feel like I am close but cannot figure out the syntax and location to put the code. I am using Access 2002. I too have a field for an email address where I want the lable not to show if there is no email address. I have seen this above:

If IsNull(Me.YourField) Then
Me.YourField_lbl.Visible = False
Else
Me.YourField_lbl.Visible = True
End If

But I am unsure of the "placeholders" and where to actually paste the code.

Here is my take:

If IsNull(DispEMail) Then
DispEMail_lbl.Visible = False
Else
DispEMail_lbl.Visible = True
End If

Am I correct to understand the "_lbl.Visible =" portion stays as written or does the "_lbl" come out?

I am tying to place this in the Detail Format area by Right Clicking the Detail header, clicking Properties and going to the Event tab. Once there I go to the Event Tab and see a line titled On Format (is this the right spot?). I click on the 3 dot dot dots and select Expression Builder. There I paste the code above and click Ok. Now back on the tab, if I click off of the line with the code I get an Invalid Syntax - operand without an operator.

Am I in the right spot? Can anyone make a suggestion to put me on the right path? Thank you.

-Nick