Best way to tackle skipping blank data in report?

[Allison]

Access 2003 SP3
Win XP SP2

I inherited a badly-designed database, from which I need to create a new
report.

What's the best way to handle this?

All in one table are the fields: CustomerNum (number, long integer, primary
key, not autonumber), and then "FirstName", "LastName", "GetsDiscount", and
"PreferredCust" (all four of those are text - not yes/no). All four of the
last ones have multiples (meaning, "FirstName1", "FirstName2", "FirstName3"
up to 20) for each employee of the customer.

I need to set up a report as a form letter to verify that all of this data
is still correct. The difficulty comes in suppressing the empty fields when
one customer only has, say 10, employees rather than all 20 fields filled.

By that I mean, I want to shrink the detail section of the report to match
the room taken up by only the text boxes that have real data in them.

The closing info is in the report footer.

What's the best way to tackle this? A pre-query? Coding?

 

[John W. Vinson]

By that I mean, I want to shrink the detail section of the report to match
the room taken up by only the text boxes that have real data in them.

Set the CanGrow and CanShrink properties of those textboxes *and* of the
form's Detail section to Yes.

 

[Allison]

This seems to work fine as long as the "empty" fields are at the end of the
list.

However, if #3 has data but #2 does not, #2 still leaves an empty hole.

Is there a way around that?

Allison

 

[John W. Vinson]

This seems to work fine as long as the "empty" fields are at the end of the
list.

However, if #3 has data but #2 does not, #2 still leaves an empty hole.

Is there a way around that?

The CanShrink will work even for intermediate date if the textboxes are
arranged vertically one above the other on the report, but only if there is no
other control beside it on the left or right. I'm not sure how you have the
report laid out!

 

[Piet Linden]

Access 2003 SP3
Win XP SP2

I inherited a badly-designed database, from which I need to create a new
report.

What's the best way to handle this?

All in one table are the fields:  CustomerNum (number, long integer, primary
key, not autonumber), and then "FirstName", "LastName", "GetsDiscount", and
"PreferredCust" (all four of those are text - not yes/no).  All four ofthe
last ones have multiples (meaning, "FirstName1", "FirstName2", "FirstName3"
up to 20) for each employee of the customer.

I need to set up a report as a form letter to verify that all of this data
is still correct.  The difficulty comes in suppressing the empty fieldswhen
one customer only has, say 10, employees rather than all 20 fields filled..

By that I mean, I want to shrink the detail section of the report to match
the room taken up by only the text boxes that have real data in them.

The closing info is in the report footer.

What's the best way to tackle this?  A pre-query?  Coding?

one way of dealing with a mess like this is to create a union query
and leave out the nulls. Hassle... but if your field names are
consistent, it's pretty easy. Then you would base the report on the
union query. I had to do this for several months - slow and painful,
but it works. About your only option if you cannot restructure the
database. If the fields fall in some sort of pattern (they're
sequential, they're all in upper case, etc) then it's pretty easy to
write code to find all of them...