2 related questions

[Sue]

How can I suppress fields that are null so that no space is utilized in
displaying my report? "Can shrink" isn't doing it...
How can I suppress a label in my report if the field it speaks to is null?
Is there a way to concatenate the expression in the data field to include the
text within my label & then suppress the whole meshugenah if the data is
blank.

 

[Allen Browne]

Right-click the label attached to the text box, and choose Change To | Text
Box.

Then set the Control Source of the new text box to an expression like this:
=IIf([City] Is Null, Null, "City")
using your field name instead of City.
Set the Can Shrink property of this new text box also.

Now both the original text box and the new one (the quazi-label) will shrink
if the field is null -- provided there is nothing else overlapping it
vertically that requires that vertical space.

 

[John Spencer]

Can shrink should do it if the field is null, but if the label is there then
it still takes up the space.

One method is to combine the label and the value.
First make sure the text control's name is not the name of any field that you
are using in the report. Simplest is to rename the control to txtMyField
instead of MyField.

Now,
Delete the label.
Set the control's source to
="My label text: " + [MyField]
Using the plus (+) sign instead of the ampersand (&) will propagate the null
value if MyField is null and the control can shrink and the section can shrink.

An alternative is to keep the label, but to use VBA code in the report
section's format event to show or hide the label when the associated field is
null (or a zero-length string)

Me.MyFieldLabel.Visible = Len(Me.MyField & "")>0

You can write that as
IF Len(Me.MyField & "")>0 THen
Me.MyFieldLabel.Visible = True
Else
Me.MyFieldLabel.Visible = False
END IF


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[Sue]

Another question...
I have a field with no associated label (don't feel I need one) & have
indicated "can shrink" for both the field and the section of the report it
lies in. The line won't suppress when null, however. Can I use some variant
of =IIf([HomePhone] Is Null,Null,"HomePhone") to solve this issue as well?
--
Thanks for your time!

Right-click the label attached to the text box, and choose Change To | Text
Box.

Then set the Control Source of the new text box to an expression like this:
=IIf([City] Is Null, Null, "City")
using your field name instead of City.
Set the Can Shrink property of this new text box also.

Now both the original text box and the new one (the quazi-label) will shrink
if the field is null -- provided there is nothing else overlapping it
vertically that requires that vertical space.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

How can I suppress fields that are null so that no space is utilized in
displaying my report? "Can shrink" isn't doing it...
How can I suppress a label in my report if the field it speaks to is null?
Is there a way to concatenate the expression in the data field to include
the
text within my label & then suppress the whole meshugenah if the data is
blank.

 

[Allen Browne]

No: that won't help if you don't want a 'label' for it.

If it is not shrinking that either
a) it is not null, or
b) something else needs the space and so it won't shrink.

It could be a zero-length string, or a space, or something else that doesn't
show but isn't null. Ask Access what it is. For example, temporarily add
another text box with a control source like this:
=([HomePhone] Is Null)
and see if it's true or false, or:
=Len([HomePhone])
If it's null, that should be blank. If it contains a number (even zero) then
it is not null.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Another question...
I have a field with no associated label (don't feel I need one) & have
indicated "can shrink" for both the field and the section of the report it
lies in. The line won't suppress when null, however. Can I use some
variant
of =IIf([HomePhone] Is Null,Null,"HomePhone") to solve this issue as well?
--
Thanks for your time!

Right-click the label attached to the text box, and choose Change To |
Text
Box.

Then set the Control Source of the new text box to an expression like
this:
=IIf([City] Is Null, Null, "City")
using your field name instead of City.
Set the Can Shrink property of this new text box also.

Now both the original text box and the new one (the quazi-label) will
shrink
if the field is null -- provided there is nothing else overlapping it
vertically that requires that vertical space.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

How can I suppress fields that are null so that no space is utilized in
displaying my report? "Can shrink" isn't doing it...
How can I suppress a label in my report if the field it speaks to is
null?
Is there a way to concatenate the expression in the data field to
include
the
text within my label & then suppress the whole meshugenah if the data
is
blank.

 

[Sue]

Thanks, Allen, for pointing me in the right direction once again. Your
suggestion did the trick!
--
Thanks for your time!

No: that won't help if you don't want a 'label' for it.

If it is not shrinking that either
a) it is not null, or
b) something else needs the space and so it won't shrink.

It could be a zero-length string, or a space, or something else that doesn't
show but isn't null. Ask Access what it is. For example, temporarily add
another text box with a control source like this:
=([HomePhone] Is Null)
and see if it's true or false, or:
=Len([HomePhone])
If it's null, that should be blank. If it contains a number (even zero) then
it is not null.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Another question...
I have a field with no associated label (don't feel I need one) & have
indicated "can shrink" for both the field and the section of the report it
lies in. The line won't suppress when null, however. Can I use some
variant
of =IIf([HomePhone] Is Null,Null,"HomePhone") to solve this issue as well?
--
Thanks for your time!

Right-click the label attached to the text box, and choose Change To |
Text
Box.

Then set the Control Source of the new text box to an expression like
this:
=IIf([City] Is Null, Null, "City")
using your field name instead of City.
Set the Can Shrink property of this new text box also.

Now both the original text box and the new one (the quazi-label) will
shrink
if the field is null -- provided there is nothing else overlapping it
vertically that requires that vertical space.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

How can I suppress fields that are null so that no space is utilized in
displaying my report? "Can shrink" isn't doing it...
How can I suppress a label in my report if the field it speaks to is
null?
Is there a way to concatenate the expression in the data field to
include
the
text within my label & then suppress the whole meshugenah if the data
is
blank.