CanGrow/Shrink won't for me!!!

  • Thread starter Thread starter Bonnie
  • Start date Start date
B

Bonnie

Using A02. I should be able to do this and can't figure
out what I'm doing wrong.

I have a query that pulls all records that meet my
criteria. One field [LongPolNum] has 13 digits, 1st 4 are
the Policy Number and next 9 are SSN. My second query
pulls only the [LongNum] field as a 'unique value' to
exclude duplicates, and parses out [PolNum]with Left,4.

From this point, my whole purpose is to count how many of
each PolNum and list only those that total 50 or more.

I created a Report that groups on the PolNum level and
detail is NotVisible. I have a calculated field in a text
box in PolNum Header: =IIf(Count([PolNum])<50,"",Count
([PolNum])). I have CanGrow/Shrink both set to Yes.
However, my report has blank lines and so, lots of space
between one listed item and the next.

I'm sure that it is something that I am doing wrong. I
have tried making the field razor thin and sizing the
header REALLY small but there is still lots of open space.

Should I be doing more in the query before I get to the
report part? I've gotten confused about which way to go
next. I have the data my boss wants but it prings ugly
and takes 20+ pages and should be only 3-4 pages long.

Any direction or advice would be GREATLY appreciated and
put to good use. Thanks in advance!
 
Bonnie,

For CanShrink to work,...
1. It has to apply to all controls across the horizontal space you
wish to have shrink.
2. The CanShrink property has to be set to Yes for the report Section
as well as to the controls.
3. It only applies if the textbox is empty.

I'm not sure about points 1 and 2 for your report, but you have a
problem with point 3. Your textbox is not empty... it either contains
a number or a "". This will work though:
=IIf(Count([PolNum])<50,Null,Count([PolNum]))

- Steve Schapel, Microsoft Access MVP
 
Bonnie said:
Using A02. I should be able to do this and can't figure
out what I'm doing wrong.

I have a query that pulls all records that meet my
criteria. One field [LongPolNum] has 13 digits, 1st 4 are
the Policy Number and next 9 are SSN. My second query
pulls only the [LongNum] field as a 'unique value' to
exclude duplicates, and parses out [PolNum]with Left,4.

From this point, my whole purpose is to count how many of
each PolNum and list only those that total 50 or more.

I created a Report that groups on the PolNum level and
detail is NotVisible. I have a calculated field in a text
box in PolNum Header: =IIf(Count([PolNum])<50,"",Count
([PolNum])). I have CanGrow/Shrink both set to Yes.
However, my report has blank lines and so, lots of space
between one listed item and the next.

I'm sure that it is something that I am doing wrong. I
have tried making the field razor thin and sizing the
header REALLY small but there is still lots of open space.

Should I be doing more in the query before I get to the
report part? I've gotten confused about which way to go
next. I have the data my boss wants but it prings ugly
and takes 20+ pages and should be only 3-4 pages long.
Click to expand...

It sounds like the group header section has some blank space
above and/or below the shrinking text box. This white space
does not shring along with the text box so it will show up
in the headers for the groups where PolNum < 50.

OTOH, if that's not the case, I have to ask if you forgot to
set the group header section's CanShrink to Yes too?

Aside from all that, it's usually a lot easier to do this
kind of report by basing it on a Totals query that filters
out the undesired data. Maybe something like:

SELECT Left(LongPolNum, 4) As PolNum,
Count(*) As CountOfPolNum
FROM thetable
GROUP BY Left(LongPolNum, 4)
HAVING Count(*) >= 50

A report on this query will be trivial to create.
 
Back
Top