Can Shrink

this is the code i have in my outer query
SELECT T1.[Vendor Number], (SELECT COUNT(*)
FROM [tblGrantCompletion] AS T2
WHERE T2.[Vendor Number] = T1.[Vendor Number]
AND T2.[ZEZA] = TRUE) AS CountOfMonths, T1.theMonth
FROM tblGrantCompletion AS T1
WHERE (((T1.ZEZA)=True) AND (((SELECT COUNT(*)
FROM [tblGrantCompletion] AS T3
WHERE T3.[Vendor Number] = T1.[Vendor Number]
AND T3.[ZEZA] = TRUE))=6))
ORDER BY T1.[Vendor Number];
(this is the outer right)
Where do I put the where clause?
I have done the cancell=is null thing. But I have to do the Where clause
along with the cancel= right?

"Ken Sheridan" wrote:

Chey:

One way to suppress blank months would be simply not to return rows with a
NULL theMonth column in the report's query by adding an extra criterion to
the outer query's WHERE clause:

WHERE theMonth IS NOT NULL

To do it in the report there a various ways. The Format event procedure has
a Cancel argument whose return value you can set to True conditionally. This
has the effect of preventing the section from printing for that row in the
report's underlying recordset, so if theMonth is Null in a row you can
prevent the detail section printing for that row with:

Cancel = IsNull(Me.theMonth)

in the detail section's Format event procedure. It does mean that nothing
else in the detail section prints for that row of course, so its an 'all or
nothing' solution. The other way would be to shrink the control, which is
fine if there are no other non-shrinking controls alongside it, but would not
work otherwise.

Another way is to set the MoveLayout property to False conditionally. This
causes the next detail to print in the same position as the current detail,
so if a row is empty the next row can be printed without moving the print
position down a row. Again this would be done in the detail section's Format
event procedure with:

MoveLayout = Not IsNull(Me.theMonth)

Returning to the question of making the Vendor Number bold, what I said in
my last post would not apply here of course as I think you have the vendor
number in the group header. To make the control in the group header bold
you'd have to examine all the rows for the current vendor number in the
underlying query in the

Ken Sheridan
Stafford, England

"Chey" wrote:

Its not so much if it is blank but if there is a gap between months.
We are working with a 6 month period. If this is not possiable then I can
work with what I have. Is there a way to a gap where a month is missing?
Just so it is easy on the eyes?

I guess I don't know what format event procedure means. I know what event
procedure is-the 3 dots. I went to properties and the event tab. Then I
don't know which one to place it under?
Thanks

"Ken Sheridan" wrote:

Chey:

One way to suppress blank months would be simply not to return rows with a
NULL theMonth column in the report's query by adding an extra criterion to
the outer query's WHERE clause:

WHERE theMonth IS NOT NULL

To do it in the report there a various ways. The Format event procedure has
a Cancel argument whose return value you can set to True conditionally. This
has the effect of preventing the section from printing for that row in the
report's underlying recordset, so if theMonth is Null in a row you can
prevent the detail section printing for that row with:

Cancel = IsNull(Me.theMonth)

in the detail section's Format event procedure. It does mean that nothing
else in the detail section prints for that row of course, so its an 'all or
nothing' solution. The other way would be to shrink the control, which is
fine if there are no other non-shrinking controls alongside it, but would not
work otherwise.

Another way is to set the MoveLayout property to False conditionally. This
causes the next detail to print in the same position as the current detail,
so if a row is empty the next row can be printed without moving the print
position down a row. Again this would be done in the detail section's Format
event procedure with:

MoveLayout = Not IsNull(Me.theMonth)

Returning to the question of making the Vendor Number bold, what I said in
my last post would not apply here of course as I think you have the vendor
number in the group header. To make the control in the group header bold
you'd have to examine all the rows for the current vendor number in the
underlying query in the

Ken Sheridan
Stafford, England

"Chey" wrote:

Its not so much if it is blank but if there is a gap between months.
We are working with a 6 month period. If this is not possiable then I can
work with what I have. Is there a way to a gap where a month is missing?
Just so it is easy on the eyes?

Chey:

Actually that's the whole query. The outer query is the main one 'outside'
the subqueries, which are the SELECT statements in parentheses. Try this:

SELECT T1.[Vendor Number],
(SELECT COUNT(*)
FROM [tblGrantCompletion] AS T2
WHERE T2.[Vendor Number] = T1.[Vendor Number]
AND T2.[ZEZA] = TRUE) AS CountOfMonths,
T1.theMonth
FROM tblGrantCompletion AS T1
WHERE T1.ZEZA=TRUE
AND theMonth IS NOT NULL
AND (SELECT COUNT(*)
FROM [tblGrantCompletion] AS T3
WHERE T3.[Vendor Number] = T1.[Vendor Number]
AND T3.[ZEZA] = TRUE)=6
ORDER BY T1.[Vendor Number];

Ken Sheridan
Stafford, England

"Chey" wrote:

this is the code i have in my outer query
SELECT T1.[Vendor Number], (SELECT COUNT(*)
FROM [tblGrantCompletion] AS T2
WHERE T2.[Vendor Number] = T1.[Vendor Number]
AND T2.[ZEZA] = TRUE) AS CountOfMonths, T1.theMonth
FROM tblGrantCompletion AS T1
WHERE (((T1.ZEZA)=True) AND (((SELECT COUNT(*)
FROM [tblGrantCompletion] AS T3
WHERE T3.[Vendor Number] = T1.[Vendor Number]
AND T3.[ZEZA] = TRUE))=6))
ORDER BY T1.[Vendor Number];
(this is the outer right)
Where do I put the where clause?
I have done the cancell=is null thing. But I have to do the Where clause
along with the cancel= right?

Chey:

In a report each section has Format and Print event procedures. The Format
event procedures run as the report is layed out by Access and the Print
procedures as it 'prints'. The Format event procedures can be used to
control the appearance of the report. The event procedures run multiple
times, in the case of the detail section at least once for each row in the
report's recordset. Sections also have a Retreat procedure which runs if
Access has to 'back up' to change the formatting, when keeping groups
together on a page for instance. This is generally used for undoing
calculations made the first time the section is formatted so they can be done
again from scratch.

To enter code into the detail section's Format event procedure you first
select the detail section by clicking on its header bar (the one which says
'Detail' on it) in design view. On the events tab of the sections properties
sheet you'll see the events listed, so its just a question of selecting one
and by using the 'build' button (the one with 3 dots) going to the VBA code
window and entering the code as new lines between the ones already in place
for the event procedure.

Ken Sheridan
Stafford, England

"Chey" wrote:

I guess I don't know what format event procedure means. I know what event
procedure is-the 3 dots. I went to properties and the event tab. Then I
don't know which one to place it under?
Thanks