Client has about 40 paragraphs of text that may or may not be inserted into a report. Those will be selected on a criteria form.
Given I don’t want odd bits of whitespace in the report due to empty fields, is there any practical way to do this on the report?

Thanks,

Peter

Combine them in a single texbox.

Methods differ, but something like:

=[field1] & (chr(13) + chr(10) + [field2]) & (chr(13) + chr(10) + [field3]) ....

You could set the Can Shrink property of the controls (text boxes, I assume) to Yes if they are lined up one above the other, and they do not have labels.

If the controls have labels, in addition to setting the Can Shrink property, maybe something like this in the section's Format event:


This applies to Access 2003. I don't know the details of how it may apply to later versions. When posting, it is best to include the Access version you are using.

Edit: I see that Walter has posted a different approach. If by "paragraph" you mean field, and there are 40 fields, it would be a long expression, but the approach could be combined with other approaches. For instance, his suggestion could be used for an address, which may have several address fields, suite numbers, etc. which would leave blank spaces if no data. It depends too on the layout of your report. Side-by-side text boxes can be trickier to manage than ones lined up vertically.
I wonder about the design of your database if there are that many separate text fields per record. It seems perhaps they should be in a related table rather than all in one large flat file.

Thanks, guys. It turns out there are 105, not 40. I'm leaning to storing them in memo fields but composing the actual document in Word. What do you think?

Do you mean you would generate the report in Word?

If there are 105 memo fields, the database design is very likely inefficient at best. Could you describe the actual situation being addressed, and the structure of the database?

Yes, automate it in Word. I think you misunderstand the DB. It is mostly for boilerplating a report of deficiencies. The user will tick off any found from a list of 105 and those have to be inserted into the report. I was thinking of one memo field to hold the descriptions, not 105!

Thanks,

Peter

I didn't understand it at all, except that there were a large number of choices to be handled in some way.

I assume you would be storing the information, so the choices would be stored in a table, then the report would be run. Or are the values being inserted into an unbound control on the report?

Assuming the information is being stored, I would expect there to be a table containing the deficiencies listing, another for the report record, and a junction table for the deficiencies that pertain to the report. It would have helped to know something about the basis for the report (is it a daily report of deficiencies, or the deficiencies for a manufacturing process, or what?). I will assume a daily report, although it doesn't really matter for purposes of the sample table structure:



For the interface, a form based on tblReport, with a subform (continuous) based on tblReportDeficiency. The Link Master and Link Child properties of the subform control (the "box" containing the subform) set to ReportID. On the subform, bound to DeficiencyID, a combo box. The combo box Row Source is a query based on tblDeficiencies. The combo box Column Count is 2, Column Widths something like 0";1.5", Bound Column 1. This will let you select as many deficiencies as needed.

For the report, you could use a report/subreport the same as for the form.