Report Fields Collapsing Row when No Value (2003)

Topic

New Reply

I hope I’m just not missing something here, but one of my users has asked me if on a form that includes an address block if there’s a way to collapse the form or remove the Address2 field when there is no second line address. Does that make sense? Say she has Name, Address, Address2, City all on separate lines one right under the other. Only occasionally will she have a two line address and she’d like to close up that space or extra line. I’ve had my Access books out, but can’t seem to see anything that would do that. Any ideas on whether it can be done or not?

Reply | Quote

Replies

In general what you proposing is done in Reports rather than in Forms.

The form is for viewing data on the screen, and entering/editing it. In this context you want to see the empty second address line.

A report is for printing. Here you want it hidden.

In the report set the CanShrink property of the second address line to yes, and also that of the section it is in.
added later
Even though your subject line talks about a Report, your actual post talks about a Form, so I have assumed you are actually talking about a Form.

Reply | Quote

Thanks for catching me on the form/report issue. I know the difference and I really mean report, but typed form! Oh, well. We tried what you mentioned, but the problem ended up being a few fields on the report that were basically on the same line as the one we wanted to Shrink. So they’ll have to live with it the way it is! If they delete the fields from the report that are on the right side of the report all works well. Thanks for you help on this one!

Reply | Quote

If you can’t get shrinking to work, you can do the work in the query instead.

SELECT Trim([title] & ” ” & [firstname] & ” ” & [surname]) AS Line1, tblBuyers.Address1 AS Line2,
IIf(Not ([address2] Is Null),[Address2],[town] & ” ” & [State] & ” ” & [Pcode]) AS line3,
IIf([address2] Is Null,” “,[Town] & ” ” & [State] & ” ” & [Pcode]) AS line4
FROM tblBuyers;

This query puts the name in line1, the first line of the address in line2.
At line3 it makes a choice. If there is a second line in the address it uses it, otherwise it uses the town, state & postcode.
At line 4 it also makes a choice, if it has already used town etc it leaves this line blank, otherwise town, state etc goes on this line.

Reply | Quote

Thanks John, I’ll show my user how to create this kind of query. Your effort is much appreciated.

Reply | Quote