Microsoft Access 2003 Report Design - Eliminating White Space

CavMom
CavMom used Ask the Experts™
on
We have a report where the design is as follows:

DocNo                 IdInfo
                            Summary

The IdInfo control is based upon an IIf statement to combine four fields of the table.  Summary is a Total Access Memo ActiveX add-in to allow for formatting of the text in the memo field.  In certain instances, IdInfo will contain no data and, therefore, leaves a blank space in the report.  What can we do to get rid of that blank space when there is no data to fill the IdInfo control?

Thanks!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2016

Commented:
set the  can grow and can shrink property of the control to YES

see this link

How to eliminate white space in reports with CanShrink and code in Access
http://support.microsoft.com/?kbid=299011

Author

Commented:
Thanks for the speedy response.  That doesn't work since the DocNo control is to the left of IdInfo.  The can grow and can shrink only work if the entire vertical area is empty.  The DocNo control needs to remain where it is and Summary needs to move up if IdInfo is empty.
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
Is the IdInfo control the TA Control you refer to?  If so:

Basically, in the OnFormat of the section the TA control is in, check the Summary Total first ... if zero, the make the TA control No visible and set it's Height to 0,  otherwise make it visible and set the height to the desired size ... or even just deal with the Height ...and leave Visible set to Yes.

mx
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
No, the IdInfo control is a text box containing an IIf statement combining four fields of the table.  The TA control (Summary) is beneath it.

DocNo (numeric control)                IdInfo (text box with IIf statement combining four fields of table)
                                                       Summary (TA control)

The IdInfo control is the one which may or may not have data.  If it does not, a blank line appears next to the DocNo.  
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
Ok ... then try the same thing I mentioned above on the IdInfo text box ... can you do that?

mx
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
Actually, try wrapping the Idinfo control in a subreport control ...  and set the Can Grow/shrink ...

mx
Database Architect / Application Developer
Top Expert 2007
Commented:
Ok here is the workaround.  Forget the subform.



DocNo (numeric control)                IdInfo ** Height = 0, Can Grow/Shrink =Yes
                                                       Summary (TA control) *** Move this up, right under IdInfo ... just a hair                                                                                              below IdInfo.

If IdInfo has no data, the TA control will be **almost** even with DocNo ... otherwise, it will display data ... and the TA will move down to normal position.

I just tried this to confirm.

mx
If you were using standard controls, I would suggest:

[DocNo label] [IDInfo + Chr(13) + Chr(10) & Summary]

But that might not work (or it's a bit more complex) due to formatting codes in Summary...

mx: I tried your suggestion, but the IDInfo textbox seems to grow *above* the DocNo control... I had to place it at 0.0007" below (smallest unit I could get). On the other hand, Summary could be aligned with it ("format / align / top"), e.g. have the same Top property.

(°v°)

Commented:
why not...

in detail_format

if isnull(IdInfo) or trim(idinfo) = "" then
  idinfo.visible = false
  summary.top = idinfo.top
else
  idinfo.visible = true
  summary.top = idinfo.top + idinfo.height + 100 '100 can be anything, its just the default spacing
end if

that way everything lines up all nice

you may also need to add
detail.height = tallestcontrol.height+tallestcontrol.top
to squeeze detail backtogether
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
"mx: I tried your suggestion, "

Mine actually seemed to almost exactly line up.  Maybe a default printer difference?

mx
mx: yeah, who knows? access reporting sure isn't an exact science!  (^v°)

Author

Commented:
I appreciate all of your responses.  Haven't had a chance to get back to the project this morning but I will before the day is out.
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
CavMom:
Thank you for coming back and closing out. Very cool.

mx

Author

Commented:
I apologize for not doing it sooner.  Today has been the first day that I've been back on this project.  Thanks for the help!
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
No problem.  We just appreciate you coming back. Many do not!

thanks again.

mx

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial