Can Shrink

[Guest]

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?

 

[Guest]

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

 

[Guest]

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

 

[Guest]

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