Can Shrink

[cjborntorun]

If anyone would be willing to accept a screenshot of my report results, that
might better describe my issue. But, if not.....I have a report that sorts
and groups products something like this and sub-totals the product form:

20oz Ravioli
20oz Tortellini
---------------
Total 20oz

9oz Ravioli
9oz Tortellini
---------------
Total 9oz

Cut Pasta
---------------
Total Cut Pasta

I don't want/need a sub-total on Cut Pasta. Is there a way to use the Can
Shrink property to hide the blank row that appears?

Thank you for your help,
CJ

 

[Allen Browne]

You can do this, if you have some way to tell Access which ones to show and
which ones to hide.

You seem to have some kind of category here you are grouping on? To make
this reliable, you should have a table to hold the valid lookup values (such
as 20oz, 9oz.) In this table, add a yes/no field to indicate whether the
total should be hidden. Let's say you name this field IsHideTotal. The table
will now have fields like this:
Category IsHideTotal
20oz No
9oz No
Cut Yes

Now create a query combining both your existing table and this lookup table.
This lets you include the IsHideTotal field in your report. You can now set
the ControlSource of the text box like this:
=IIf([IsHideTotal], Null, [Category])
Set the text box's CanShrink property to Yes. For those categories where
IsHideTotal is true, the text box contains Null, and so it shrinks. For
those categories where IsHideTotal is not true, it displays the category,so
it doesn't shrink.

 

[cjborntorun]

Hi - thank you, I have successfully hidden the description for "Cut Pasta".
Now, how do I go about hiding the related sub-total amounts for Cut Pasta?
Horizontally across the page, I have sub-total controls for 2005$, 2006$,
2007$, 2008$. Thanks!

You can do this, if you have some way to tell Access which ones to show and
which ones to hide.

You seem to have some kind of category here you are grouping on? To make
this reliable, you should have a table to hold the valid lookup values (such
as 20oz, 9oz.) In this table, add a yes/no field to indicate whether the
total should be hidden. Let's say you name this field IsHideTotal. The table
will now have fields like this:
Category IsHideTotal
20oz No
9oz No
Cut Yes

Now create a query combining both your existing table and this lookup table.
This lets you include the IsHideTotal field in your report. You can now set
the ControlSource of the text box like this:
=IIf([IsHideTotal], Null, [Category])
Set the text box's CanShrink property to Yes. For those categories where
IsHideTotal is true, the text box contains Null, and so it shrinks. For
those categories where IsHideTotal is not true, it displays the category,so
it doesn't shrink.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

If anyone would be willing to accept a screenshot of my report results,
that
might better describe my issue. But, if not.....I have a report that
sorts
and groups products something like this and sub-totals the product form:

20oz Ravioli
20oz Tortellini
---------------
Total 20oz

9oz Ravioli
9oz Tortellini
---------------
Total 9oz

Cut Pasta
---------------
Total Cut Pasta

I don't want/need a sub-total on Cut Pasta. Is there a way to use the
Can Shrink property to hide the blank row that appears?

 

[cjborntorun]

REVISION: Disregard my last comment. I played around with the formula you
gave me some more and successfully included it with the controls for the
amounts. My last problem related to this is that I have some horizontal and
vertical lines drawn on my report to separate some of the data. Is there a
way to "shrink" those when category is null? I don't see a can shrink option

Hi - thank you, I have successfully hidden the description for "Cut Pasta".
Now, how do I go about hiding the related sub-total amounts for Cut Pasta?
Horizontally across the page, I have sub-total controls for 2005$, 2006$,
2007$, 2008$. Thanks!

You can do this, if you have some way to tell Access which ones to show and
which ones to hide.

You seem to have some kind of category here you are grouping on? To make
this reliable, you should have a table to hold the valid lookup values (such
as 20oz, 9oz.) In this table, add a yes/no field to indicate whether the
total should be hidden. Let's say you name this field IsHideTotal. The table
will now have fields like this:
Category IsHideTotal
20oz No
9oz No
Cut Yes

Now create a query combining both your existing table and this lookup table.
This lets you include the IsHideTotal field in your report. You can now set
the ControlSource of the text box like this:
=IIf([IsHideTotal], Null, [Category])
Set the text box's CanShrink property to Yes. For those categories where
IsHideTotal is true, the text box contains Null, and so it shrinks. For
those categories where IsHideTotal is not true, it displays the category,so
it doesn't shrink.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

If anyone would be willing to accept a screenshot of my report results,
that
might better describe my issue. But, if not.....I have a report that
sorts
and groups products something like this and sub-totals the product form:

20oz Ravioli
20oz Tortellini
---------------
Total 20oz

9oz Ravioli
9oz Tortellini
---------------
Total 9oz

Cut Pasta
---------------
Total Cut Pasta

I don't want/need a sub-total on Cut Pasta. Is there a way to use the
Can Shrink property to hide the blank row that appears?

 

[Allen Browne]

As you say, lines don't have a Can Shrink property.

Could you use a text box instead of a line?
Turn on its border, and set it to (say) 0.01" wide.
(I haven't tried, but it should still shrink when null.)

Alternatively, you may be able to adjust the Height of the line in the
Format event of the section that contains it. (Might work in A2002 or
later.)

Stephen Lebans has a solution that does work for growing lines vertically:
http://www.lebans.com/cangrow.htm

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

REVISION: Disregard my last comment. I played around with the formula
you
gave me some more and successfully included it with the controls for the
amounts. My last problem related to this is that I have some horizontal
and
vertical lines drawn on my report to separate some of the data. Is there
a
way to "shrink" those when category is null? I don't see a can shrink
option

Hi - thank you, I have successfully hidden the description for "Cut
Pasta".
Now, how do I go about hiding the related sub-total amounts for Cut
Pasta?
Horizontally across the page, I have sub-total controls for 2005$, 2006$,
2007$, 2008$. Thanks!

You can do this, if you have some way to tell Access which ones to show
and
which ones to hide.

You seem to have some kind of category here you are grouping on? To
make
this reliable, you should have a table to hold the valid lookup values
(such
as 20oz, 9oz.) In this table, add a yes/no field to indicate whether
the
total should be hidden. Let's say you name this field IsHideTotal. The
table
will now have fields like this:
Category IsHideTotal
20oz No
9oz No
Cut Yes

Now create a query combining both your existing table and this lookup
table.
This lets you include the IsHideTotal field in your report. You can now
set
the ControlSource of the text box like this:
=IIf([IsHideTotal], Null, [Category])
Set the text box's CanShrink property to Yes. For those categories
where
IsHideTotal is true, the text box contains Null, and so it shrinks. For
those categories where IsHideTotal is not true, it displays the
category,so
it doesn't shrink.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

If anyone would be willing to accept a screenshot of my report
results,
that
might better describe my issue. But, if not.....I have a report that
sorts
and groups products something like this and sub-totals the product
form:

20oz Ravioli
20oz Tortellini
---------------
Total 20oz

9oz Ravioli
9oz Tortellini
---------------
Total 9oz

Cut Pasta
---------------
Total Cut Pasta

I don't want/need a sub-total on Cut Pasta. Is there a way to use
the
Can Shrink property to hide the blank row that appears?