Stupid question, but If you make something invisable, then on top of that
have a field, that is can shrink. Does that count, or will it not shrink
above the invisable text box?

The control will still shrink regardless of the hidden control If there is
anything visible alongside the shrinkable control, however, then it won't
shrink. For instance if you have a label you want to hide if a control
shrinks if it has no data then you should hide the label by putting code in
the relevant section's Format event procedure, e.g.

Me.MyLabel.Visible = Not IsNull(Me.MyBoundControl)

The bound control will then shrink to nothing if Null and the label will be
hidden.

Ken Sheridan
Stafford, England


"Chey" wrote:

> Stupid question, but If you make something invisable, then on top of that
> have a field, that is can shrink. Does that count, or will it not shrink
> above the invisable text box?

So I took out all the labels
But I have a field that is invisable, then on top of that I have a formual.
Then I have a subreport that I have done the same with.
How do I get the subreport to do the same?
I don't want any blank pages.
Thanks

"Ken Sheridan" wrote:

> The control will still shrink regardless of the hidden control If there is
> anything visible alongside the shrinkable control, however, then it won't
> shrink. For instance if you have a label you want to hide if a control
> shrinks if it has no data then you should hide the label by putting code in
> the relevant section's Format event procedure, e.g.
>
> Me.MyLabel.Visible = Not IsNull(Me.MyBoundControl)
>
> The bound control will then shrink to nothing if Null and the label will be
> hidden.
>
> Ken Sheridan
> Stafford, England
>
>
> "Chey" wrote:
>
> > Stupid question, but If you make something invisable, then on top of that
> > have a field, that is can shrink. Does that count, or will it not shrink
> > above the invisable text box?

Are you saying that you want to hide the subreport if it has no data? If so,
reports have a NoData property, so you can hide the subreport 'control', i.e.
the control in the parent report which houses the subreport, by putting code
in the Format event procedure of whichever section of the parent report
contains the subreport:

Me.MySubreportControl.Visible = Me.MySubreportControl.Report.HasData

Ken Sheridan
Stafford, England

"Chey" wrote:

> So I took out all the labels
> But I have a field that is invisable, then on top of that I have a formual.
> Then I have a subreport that I have done the same with.
> How do I get the subreport to do the same?
> I don't want any blank pages.
> Thanks
>

I am actually trying to achive something else, but tried this route first
I have a query and all I want to do is view a select part of data.
I want to see if a provider has had a check box for a period of 6 months.
So what I did was make a report with a vendor number
and on my subreport the months. Everything looked okay until, I did the
formula
iif a field is greater than 6 then visable if not then null. That worked
to. However I had to many blank pages.

Now what. Any suggestions.


"Ken Sheridan" wrote:

> Are you saying that you want to hide the subreport if it has no data? If so,
> reports have a NoData property, so you can hide the subreport 'control', i.e.
> the control in the parent report which houses the subreport, by putting code
> in the Format event procedure of whichever section of the parent report
> contains the subreport:
>
> Me.MySubreportControl.Visible = Me.MySubreportControl.Report.HasData
>
> Ken Sheridan
> Stafford, England
>
> "Chey" wrote:
>
> > So I took out all the labels
> > But I have a field that is invisable, then on top of that I have a formual.
> > Then I have a subreport that I have done the same with.
> > How do I get the subreport to do the same?
> > I don't want any blank pages.
> > Thanks
> >

I'd think there's probably an easier way using a query, on which you can then
base a simple report. If you can post details of the table(s) involved and,
if there's more than one, how they are related, along with an exact
description of what you want then hopefully I might be able to come up with
something.

I don't know what time it is where you are but its time to hit the sack
here, so I'll pick up anything you post tomorrow.

Ken Sheridan
Stafford, England

"Chey" wrote:

> I am actually trying to achive something else, but tried this route first
> I have a query and all I want to do is view a select part of data.
> I want to see if a provider has had a check box for a period of 6 months.
> So what I did was make a report with a vendor number
> and on my subreport the months. Everything looked okay until, I did the
> formula
> iif a field is greater than 6 then visable if not then null. That worked
> to. However I had to many blank pages.
>
> Now what. Any suggestions.

If you could help that would be great.
I have asked this question numerouse times, and have had no good luck. I
take a little from everyone to try to get this to work
I have one table
tblGrant Completion-March
This has fields
ZEZA-y/n box
Vendor Number
theMonth

I have a query for the main report-this only has the vendor number
and a query for the subreport-this has vendor number, month, ZEZE
I have an unboud that has count in it on the subreport
I only want to see when ZEZA has been check for a peroid of 6 months.
Right now I can see how many times it has been checked for each provider.
I then did and if statement to make it visable. This casused just a blank
spot. Then I did the can shrink. This still left a blank spot.

What I want to see
Ex

ABC12345 (6)
January 05
Februay 05
March 05
April 05
May 05
June 05

ABC12345-Vendor Number
(6) amount of months
Jan 05-June 05 months that ZEZA were checked.

Please help me, I don't know what to do.
I need to know how to write the query properly and even if I need a subreport.
Plus where in the query to write this.

"Ken Sheridan" wrote:

> I'd think there's probably an easier way using a query, on which you can then
> base a simple report. If you can post details of the table(s) involved and,
> if there's more than one, how they are related, along with an exact
> description of what you want then hopefully I might be able to come up with
> something.
>
> I don't know what time it is where you are but its time to hit the sack
> here, so I'll pick up anything you post tomorrow.
>
> Ken Sheridan
> Stafford, England
>
> "Chey" wrote:
>
> > I am actually trying to achive something else, but tried this route first
> > I have a query and all I want to do is view a select part of data.
> > I want to see if a provider has had a check box for a period of 6 months.
> > So what I did was make a report with a vendor number
> > and on my subreport the months. Everything looked okay until, I did the
> > formula
> > iif a field is greater than 6 then visable if not then null. That worked
> > to. However I had to many blank pages.
> >
> > Now what. Any suggestions.
>

Try this:

SELECT T1.[Vendor Number],
(SELECT COUNT(*)
FROM [tblGrant Completion-March] AS T2
WHERE T2. [Vendor Number] = T1. [Vendor Number]
AND T2.[ZEZA] = TRUE) AS CountOfMonths,
theMonth
FROM [tblGrant Completion-March] AS T1
WHERE T1.ZEZA = TRUE
AND
(SELECT COUNT(*)
FROM [tblGrant Completion-March] AS T3
WHERE T3. [Vendor Number] = T1. [Vendor Number]
AND T3.[ZEZA] = TRUE)>=6;

The way it works is this: The subquery in the outer query's SELECT clause
counts the number of rows in the table where ZEZA is TRUE and the Vendor
Number is the same as the current Vendor number in the outer query. The two
instances of the table are distinguished by giving them aliases T1 and T2.
The WHERE clause of the outer query restricts the rows returned to those
where ZEZA = TRUE. The subquery in the outer query's WHERE clause
restricts the rows returned by the outer query to those where there are at
least 6 whwre ZEZA = TRUE.

You can then base a report on this and group the report by Vendor Number.
Put Vendor Number and CountofMonths in a group header and theMonth in the
detail section. No subreport is needed.

Ken Sheridan
Stafford, England

"Chey" wrote:

> If you could help that would be great.
> I have asked this question numerouse times, and have had no good luck. I
> take a little from everyone to try to get this to work
> I have one table
> tblGrant Completion-March
> This has fields
> ZEZA-y/n box
> Vendor Number
> theMonth
>
> I have a query for the main report-this only has the vendor number
> and a query for the subreport-this has vendor number, month, ZEZE
> I have an unboud that has count in it on the subreport
> I only want to see when ZEZA has been check for a peroid of 6 months.
> Right now I can see how many times it has been checked for each provider.
> I then did and if statement to make it visable. This casused just a blank
> spot. Then I did the can shrink. This still left a blank spot.
>
> What I want to see
> Ex
>
> ABC12345 (6)
> January 05
> Februay 05
> March 05
> April 05
> May 05
> June 05
>
> ABC12345-Vendor Number
> (6) amount of months
> Jan 05-June 05 months that ZEZA were checked.
>
> Please help me, I don't know what to do.
> I need to know how to write the query properly and even if I need a subreport.
> Plus where in the query to write this.
>

No so I understand this righ this code goes in SQL View under the query right?
Then how do I do the other query it askes me to save the SQL query then can
I build another query to display on my report?

"Ken Sheridan" wrote:

> Try this:
>
> SELECT T1.[Vendor Number],
> (SELECT COUNT(*)
> FROM [tblGrant Completion-March] AS T2
> WHERE T2. [Vendor Number] = T1. [Vendor Number]
> AND T2.[ZEZA] = TRUE) AS CountOfMonths,
> theMonth
> FROM [tblGrant Completion-March] AS T1
> WHERE T1.ZEZA = TRUE
> AND
> (SELECT COUNT(*)
> FROM [tblGrant Completion-March] AS T3
> WHERE T3. [Vendor Number] = T1. [Vendor Number]
> AND T3.[ZEZA] = TRUE)>=6;
>
> The way it works is this: The subquery in the outer query's SELECT clause
> counts the number of rows in the table where ZEZA is TRUE and the Vendor
> Number is the same as the current Vendor number in the outer query. The two
> instances of the table are distinguished by giving them aliases T1 and T2.
> The WHERE clause of the outer query restricts the rows returned to those
> where ZEZA = TRUE. The subquery in the outer query's WHERE clause
> restricts the rows returned by the outer query to those where there are at
> least 6 whwre ZEZA = TRUE.
>
> You can then base a report on this and group the report by Vendor Number.
> Put Vendor Number and CountofMonths in a group header and theMonth in the
> detail section. No subreport is needed.
>
> Ken Sheridan
> Stafford, England
>
> "Chey" wrote:
>
> > If you could help that would be great.
> > I have asked this question numerouse times, and have had no good luck. I
> > take a little from everyone to try to get this to work
> > I have one table
> > tblGrant Completion-March
> > This has fields
> > ZEZA-y/n box
> > Vendor Number
> > theMonth
> >
> > I have a query for the main report-this only has the vendor number
> > and a query for the subreport-this has vendor number, month, ZEZE
> > I have an unboud that has count in it on the subreport
> > I only want to see when ZEZA has been check for a peroid of 6 months.
> > Right now I can see how many times it has been checked for each provider.
> > I then did and if statement to make it visable. This casused just a blank
> > spot. Then I did the can shrink. This still left a blank spot.
> >
> > What I want to see
> > Ex
> >
> > ABC12345 (6)
> > January 05
> > Februay 05
> > March 05
> > April 05
> > May 05
> > June 05
> >
> > ABC12345-Vendor Number
> > (6) amount of months
> > Jan 05-June 05 months that ZEZA were checked.
> >
> > Please help me, I don't know what to do.
> > I need to know how to write the query properly and even if I need a subreport.
> > Plus where in the query to write this.
> >

Yep, you do enter this in SQL view. The first thing then is to confirm that
it returns the correct rows by switching to datasheet view. If you are happy
that it does then the question is what else do you need to show in the
report. Say you want to show vendors' names from a Vendors table which the
Vendor Number field in tblGrant Completion-March references, then you'd add
the vendors table to the query and join it to tblGrant Completion-March on
the Vendor Number fields. I'd normally do this by simply typing in some
extra SQL, but you can also do it by switching to design view then adding the
table and joining it to tblGrant Completion-March visually in the usual way.
You can then drag whatever other fields you need into blank columns the query
design grid. Once you are satisfied that you have all the necessary fields
for the report save the query under any suitable name.

As far as the report is concerned you just enter the query name as its
RecordSource property in the report's properties sheet. As you no longer
need a subreport you can discard that. In the report's sorting and grouping
dialogue you'd group on either the Vendor Number or a vendor Name as the
first group level, providing the names are unique, and give it a group
header. The advantage of grouping on the name is that the report will show
the vendors in alphabetical order.

Fields such as Vendor name relating to the Vendor go in the group header so
you only see them once in the report. The Months go in the detail so that
they are listed individually.

Ken Sheridan
Stafford, England

"Chey" wrote:

> No so I understand this righ this code goes in SQL View under the query right?
> Then how do I do the other query it askes me to save the SQL query then can
> I build another query to display on my report?
>