Can Grow / Can Shrink not working

[icantprint]

I hope I'm not asking another stupid question, but I've been playing with this for days, even looked for a VBA solution, and coming up with nothing. I've attached my design view details and the result of the print preview. These are all text boxes with no labels. Not everyone has a "name_2" or a "mailing_2" so I'd like those suppressed in the report when blank.

I've set all these text boxes to Can Grow and Can Shrink, and also set the detail properties to the same. As you can see, I still have blank lines where there shouldn't be any. I tried using "=Iif(Trim(nz([mailing2],""))="","",[mailing2])" but I'm pretty sure this isn't the proper case for it.

Probably another issue, but I'd like the city, state and zip fields to appear more naturally, but that's probably another question.

I appreciate the help again!

 

[GaP42]

Suspect the "grey" space between controls is adding to the space seen in print preview - compress the text box controls so no vertical spaces between them and check print preview again.

 

[CJ_London]

do you have any controls to the right?

 

[Pat Hartman]

Welcome to AWF

The controls shrink but that doesn't remove the space above and below. So the space between Name and Name_2 and Name_2 and Mailing_1 remains. Try tightening up the space between the lines to see if that helps. Otherwise, you can use a single control that concatenates all the address fields and so the one control contains all the lines of the address. You need to make use of both the & and + concatenation operators to ensure that null values disappear. Also, be aware that unless you set the Allow Zero Length Strings property for the column on the table design, the field won't necessarily be null and therefore would never shrink away anyway because a ZLS is "something" whereas Null is "nothing" in this case and "something" persists rather than being shrunk away.

PS, "Name" is a really poor choice as a column name. Why? Because Me.Name is not a reference to YOUR Name control. It is a reference to the name of the form. As long as you never use VBA, you won't see this type of problem but once you use VBA, then Debug.Print Me.Name will show "rptMyLabels" rather than "John Smith".

Here's a database that shows some of the bad things that happen when you use function or property names as column names.

Useful code AND the effects of using bad column names

This database has several useful code procedures that you can copy into your BE to fix up defaults you don't like: 1. Change the allow ZLS string property to No If you want to make a text field required, it makes no sense at all to have the Allow ZLS property set to Yes. 2. Turn off sub...

www.access-programmers.co.uk

When Access tells you to not use a certain value as a control name, it is best to take the advice even if you don't understand the reason.

 

[icantprint]

do you have any controls to the right?

No, no controls to the right

 

[DHookom]

The Can Shrink would only work with Null. Your expression is changing Null to a zero length string which goes against your desired results. You might want to make all possible Null text boxes to a height of 0.04" and set the section to can shrink.

 

[icantprint]

Suspect the "grey" space between controls is adding to the space seen in print preview - compress the text box controls so no vertical spaces between them and check print preview again.

That makes no difference at all. I originally had them without spaces, then after learning having them touching/overlapping could affect the grow/shrink. I just put them back, and it's the same.

 

[arnelgp]

you do not need to add IIF() to your report.
select all your textbox and set it's CanGrow and CanShrink property to Yes.
now adjust the height of Mailing2 textbox to 0.
adjust the remaing textboxes after Mailing2 textbox so that
the space between them are the same (uniform).

test your report.

 

[CJ_London]

Leaving any horizontal space between controls will result in a double space when one is hidden

 

[GaP42]

you can use a single control that concatenates all the address fields and so the one control contains all the lines of the address. You need to make use of both the & and + concatenation operators to ensure that null values disappear. Also, be aware that unless you set the Allow Zero Length Strings property for the column on the table design, the field won't necessarily be null and therefore would never shrink away anyway because a ZLS is "something" whereas Null is "nothing" in this case and "something" persists rather than being shrunk away.

next step: See above re Pat's suggestion re nulls and ZLS, / and post #6

 

[Pat Hartman]

@icantprint Have you determined whether your table columns contain ZLS or null? You need to figure that out and you need to understand how to either eliminate ZLS entirely (what I do) or handle it correctly in this instance. If you don't know how to concatenate the name/addr fields correctly to achieve your result, let us know and someone will show you the pattern.

 

[Mark_]

Were I doing this, I would create your Mailing address in the query that drives your report rather than trying to mess about with the controls. Something like

AddyBlock: [CustName_1] & IIF( CustName_2 = "", "", Chr(13) & Chr(10) & CustName_2) & Chr(13) & Chr(10) & Mailing_1 & IIF( Mailing_2 = "", "", Chr(13) & Chr(10) & Mailing_2) & Chr(13) & Chr(10) & [City] & ", " & [State] & " " & [ZIP]

This would return

Name
Mailing
City, State ZIP

or

Name
Name 2
Mailing
Mailing 2
City, State ZIP

as needed. You are just building this up as you go. Chr(13) & Chr(10) adds the return between lines.

First I'd also sit down and work out better naming conventions for your fields though.

You may also be interested in using expressions in Access

 

[Pat Hartman]

Your solution only handles ZLS. It doesn't handle null.