Hide text box if null value

[Guest]

I am creating a mailing report and would like to ignore address fields if
they are blank. I have tried the 'can shrink' which does work if the text
boxes aren't touching each other, but the gap above and below the shrunken
text box are still there so the address still has a space in the middle. If
the text boxes are touching each other then the can shrink doesn't seem to
work.

I have tried concatenating the string and using & Chr(13) & Chr(10) &
between the fields, but that obviously still leaves a line space.

In the control source expression builder I've started with this but don't
know what to put next.

IIf ( IsNull ( [Address] ) , «truepart», «falsepart»)

Any help would be greatly appreciated

 

[Jeff Boyce]

Clare

I've not noticed the problem you mention re: touching controls. Have you
also set the "Can Shrink" for the section, as a whole?

 

[Guest]

Well I don't know how but it's all working now, I set detail to can shrink as
well and had one address with all the boxes touching and one where they
weren't. I kept putting something into the field and then taking it away to
see how things looked. Somehow now when all the textboxes are touching it
does seem to shrink and not leave that large gap.

I think it all has to do with how you place them next to each other. I have
identical text boxes with all the same properties (only the distance from the
top is different) and one lot works another doesnt. Perhaps it's one of those
things that you should just accept and not question

 

[Marshall Barton]

Well I don't know how but it's all working now, I set detail to can shrink as
well and had one address with all the boxes touching and one where they
weren't. I kept putting something into the field and then taking it away to
see how things looked. Somehow now when all the textboxes are touching it
does seem to shrink and not leave that large gap.

I think it all has to do with how you place them next to each other. I have
identical text boxes with all the same properties (only the distance from the
top is different) and one lot works another doesnt. Perhaps it's one of those
things that you should just accept and not question

Two things to keep in mind here. One is that it is ok if
two controls exactly touch top to bottom, but it is not ok
if they overlap by even the smallest amount. This can be
very difficult to achieve without help. What I do is set
the report's GridX and GridY properties to 20 and set each
text box's Height to a multiple of .05. This way the Snap
to Grid option can position them exactly as needed.

The other thing is with side by side controls. While either
control will shrink as appropriate, it is near meaningless
because the section can not shrink unless both controls
shrink.

The situation when you use a single text box will supress
the new lines if you judiciously mix + and & concatenations.
For example:
=FullName & (Chr(13) + Chr(10) + Address1) & (Chr(13) +
Chr(10) + Address2) & . . .

 

[Guest]

Marshall,

I had thought it could have been something to do with getting lucky with
positioning them close but not overlapping. That + and & has solved
everything though, it looks so much neater. No more trying to light
everything up perfectly now.

Thanks,

Clare

 

[Guest]

For future reference, if you have an issue with controls overlapping, first
slightly overlap all text boxes in question and then highlight them all.
Next select format --> Horizontal or Vertical --> Make Equal. This will
force all text boxes to be placed flushly against one another. This removes
the trial and error guessing of placing them next to each other manually.

Justin

 

[Brandon W]

Thanks Justin! I've been trying to find an easier way to do this for quite a
while.

For those using Access 2007, the commands are in the Arrange tab under
Position. There are buttons for "Equal Horizontal" and "Equal Vertical" as
well as increase and decrease buttons.