Hide subreport and main report record if subreport running sum is zero

[forcefield via AccessMonster.com]

My subreport and main report are linked via master and child relationship.
The subreport has a running sum and I have placed it in the main report item
footer. How do you prevent the subreport from not displaying that item if it
has a zero running sum?

The structure of the main report whose record source is a query is as follows:

Report header consists of title of the report
Page header consists of headings for the detail section
Item header consists of Item
NameofSupplier Header consists of supplier names
Detail consists of Inv Date, Inv No, Unit price etc
Item Footer (where the subreport is located)
Page Footer consists of Page No

The subreport is pulled from a query has a structure
Page Header
Item Header
Detail Section (whose visible is set to NO) consists of PurchaseDate, Item
PurchaseQty, OutQty

Item Footer (visible Yes) consists of RunSum
whose record source is DSum(" Nz([PurchaseQty])-Nz([OutQty]) ",
"tblSubProduct","(QtyID<=" & Nz([QtyID],0) & ") And ([Item] = '" & Replace
([Item],"'","''") & "')") as appeared in the query.

The subreport is dragged into the item footer of the main report. When I ran
it, the report displays all the items including those with running sum zero.
What I would to do is only display those items in the main report and its
running sum in the subreport if the running sum in the subreport is not zero.
Can someone please help?

 

[Marshall Barton]

My subreport and main report are linked via master and child relationship.
The subreport has a running sum and I have placed it in the main report item
footer. How do you prevent the subreport from not displaying that item if it
has a zero running sum?

The structure of the main report whose record source is a query is as follows:

Report header consists of title of the report
Page header consists of headings for the detail section
Item header consists of Item
NameofSupplier Header consists of supplier names
Detail consists of Inv Date, Inv No, Unit price etc
Item Footer (where the subreport is located)
Page Footer consists of Page No

The subreport is pulled from a query has a structure
Page Header
Item Header
Detail Section (whose visible is set to NO) consists of PurchaseDate, Item
PurchaseQty, OutQty

Item Footer (visible Yes) consists of RunSum
whose record source is DSum(" Nz([PurchaseQty])-Nz([OutQty]) ",
"tblSubProduct","(QtyID<=" & Nz([QtyID],0) & ") And ([Item] = '" & Replace
([Item],"'","''") & "')") as appeared in the query.

The subreport is dragged into the item footer of the main report. When I ran
it, the report displays all the items including those with running sum zero.
What I would to do is only display those items in the main report and its
running sum in the subreport if the running sum in the subreport is not zero.

I think you can use code along these lines in the main
report's Item footer section's Format event:

Me.subreport.Visible = (Me.subreport.Report.runsumtextbox>0)

Note: It would be more complicated if there's a chance that
the subreport has no records.

I don't see why you need to use DSum. Wouldn't using Sum be
sufficient?

 

[forcefield via AccessMonster.com]

Thank you for replying. I tried your suggestion. The subreport is hidden but
the main report still displays those items.

My subreport and main report are linked via master and child relationship.
The subreport has a running sum and I have placed it in the main report item

[quoted text clipped - 26 lines]

What I would to do is only display those items in the main report and its
running sum in the subreport if the running sum in the subreport is not zero.

I think you can use code along these lines in the main
report's Item footer section's Format event:

Me.subreport.Visible = (Me.subreport.Report.runsumtextbox>0)

Note: It would be more complicated if there's a chance that
the subreport has no records.

I don't see why you need to use DSum. Wouldn't using Sum be
sufficient?

 

[Marshall Barton]

If you want to hide the main report Item footer section when
the subreport total is zero, try adding this kind of thing
to the main report detail section's Format event procedureL

Me.itemfooter.Visible=(Me.subreport.Report.runsumtextbox>0)

But I don't see how that can interact with the supplier
group and detail sections.

If you want, as I suspect, to hide the item group, supplier
group and detail, then the subreport total can't be used to
hide sections that are processed before the subreport. That
means you should change the main report's record source
query to calculate the total for each item and filter the
items with a zero total out of the query. The calculation
will probably require a subquery.
--
Marsh
MVP [MS Access]

Thank you for replying. I tried your suggestion. The subreport is hidden but
the main report still displays those items.

My subreport and main report are linked via master and child relationship.
The subreport has a running sum and I have placed it in the main report item

[quoted text clipped - 26 lines]

What I would to do is only display those items in the main report and its
running sum in the subreport if the running sum in the subreport is not zero.

I think you can use code along these lines in the main
report's Item footer section's Format event:

Me.subreport.Visible = (Me.subreport.Report.runsumtextbox>0)

 

[forcefield via AccessMonster.com]

Thank you for your input. I guess I will have to use subquery via forums
although I have not use it before.

If you want to hide the main report Item footer section when
the subreport total is zero, try adding this kind of thing
to the main report detail section's Format event procedureL

Me.itemfooter.Visible=(Me.subreport.Report.runsumtextbox>0)

But I don't see how that can interact with the supplier
group and detail sections.

If you want, as I suspect, to hide the item group, supplier
group and detail, then the subreport total can't be used to
hide sections that are processed before the subreport. That
means you should change the main report's record source
query to calculate the total for each item and filter the
items with a zero total out of the query. The calculation
will probably require a subquery.

Thank you for replying. I tried your suggestion. The subreport is hidden but
the main report still displays those items.

[quoted text clipped - 9 lines]

 

[Marshall Barton]

The subquery could be something along these lines as a
calculated field in the main report's record source query:

(SELECT Sum(X.[some field]) FROM [some table] As X WHERE
X.Item = [item table].Item)

with the criteria for it:
<>0

I know that's oretty vague, but I can;t be more specific
without more details about your tables, their fields and how
they are related.
--
Marsh
MVP [MS Access]

Thank you for your input. I guess I will have to use subquery via forums
although I have not use it before.

If you want to hide the main report Item footer section when
the subreport total is zero, try adding this kind of thing
to the main report detail section's Format event procedureL

Me.itemfooter.Visible=(Me.subreport.Report.runsumtextbox>0)

But I don't see how that can interact with the supplier
group and detail sections.

If you want, as I suspect, to hide the item group, supplier
group and detail, then the subreport total can't be used to
hide sections that are processed before the subreport. That
means you should change the main report's record source
query to calculate the total for each item and filter the
items with a zero total out of the query. The calculation
will probably require a subquery.

Thank you for replying. I tried your suggestion. The subreport is hidden but
the main report still displays those items.

[quoted text clipped - 9 lines]

Me.subreport.Visible = (Me.subreport.Report.runsumtextbox>0)

 

[forcefield via AccessMonster.com]

On my actual tblSubProduct is
PurchaseDate Item PurchaseQty OutQty RunSum
28/02/2008 Hacksaw 20 8
12
12/03/2008 Hacksaw 5 15
2
24/03/2008 Hacksaw 8 10
0

Record source for main report:
SELECT tblProduct.InvDate, tblProduct.SerialNo, tblProduct.Item, tblProduct.
Supplier, tblProduct.UnitPrice, tblProduct.InvNo
FROM tblProduct;

Record source for SubReport
SELECT tblSubProduct.QtyID, tblSubProduct.Item, tblSubProduct.PurchaseQty,
tblSubProduct.OutQty, tblSubProduct.PurchaseDate, DSum(" Nz([PurchaseQty])-Nz
([OutQty])","tblSubProduct","(QtyID<=" & NZ([QtyID],0) & ") And ([Item] =
'" & Replace([Item],"'","''") & "')") AS RunSum
FROM tblSubProduct
ORDER BY tblSubProduct.QtyID DESC , tblSubProduct.Item DESC;

Sample of report:

Hacksaw
GridHard Pte Ltd
12/03/08 K38455 P3729 $14.00
Iron Ware Pte Ltd
28/02/2008 K29468 P3948 $14.00
24/03/2008 K38571 P3856 $15.00
Total Qty at Hand: 0

(I expected none to be displayed)

When I use subquery:

Record source for the main report:

SELECT tblProduct.InvDate, tblProduct.SerialNo, tblProduct.Item, tblProduct.
Supplier, tblProduct.UnitPrice, tblProduct.InvNo
FROM tblProduct
WHERE (((Exists (SELECT tblSubProduct.QtyID, tblSubProduct.Item,
tblSubProduct.PurchaseQty, tblSubProduct.OutQty, tblSubProduct.PurchaseDate,
DSum(" Nz([PurchaseQty])-Nz([OutQty])","tblSubProduct","(QtyID<=" & NZ([QtyID]
,0) & ") And ([Item] = '" & Replace([Item],"'","''") & "')") AS RunSum
FROM tblSubProduct
WHERE (((DSum(" Nz([PurchaseQty])-Nz([OutQty])","tblSubProduct","(QtyID<=" &
NZ([QtyID],0) & ") And ([Item] = '" & Replace([Item],"'","''") & "')"))>0))
ORDER BY tblSubProduct.QtyID, tblSubProduct.Item)<>False));

Sample of report
Hacksaw
GridHard Pte Ltd
12/03/08 K38455 P3729 $14.00
Iron Ware Pte Ltd
28/02/2008 K29468 P3948 $14.00
24/03/2008 K38571 P3856 $15.00
Total Qty at Hand: 2

The subquery could be something along these lines as a
calculated field in the main report's record source query:

(SELECT Sum(X.[some field]) FROM [some table] As X WHERE
X.Item = [item table].Item)

with the criteria for it:
<>0

I know that's oretty vague, but I can;t be more specific
without more details about your tables, their fields and how
they are related.

Thank you for your input. I guess I will have to use subquery via forums
although I have not use it before.

[quoted text clipped - 20 lines]

 

[Marshall Barton]

On my actual tblSubProduct is
PurchaseDate Item PurchaseQty OutQty RunSum
28/02/2008 Hacksaw 20 8
12
12/03/2008 Hacksaw 5 15
2
24/03/2008 Hacksaw 8 10
0

Record source for main report:
SELECT tblProduct.InvDate, tblProduct.SerialNo, tblProduct.Item, tblProduct.
Supplier, tblProduct.UnitPrice, tblProduct.InvNo
FROM tblProduct;

Record source for SubReport
SELECT tblSubProduct.QtyID, tblSubProduct.Item, tblSubProduct.PurchaseQty,
tblSubProduct.OutQty, tblSubProduct.PurchaseDate, DSum(" Nz([PurchaseQty])-Nz
([OutQty])","tblSubProduct","(QtyID<=" & NZ([QtyID],0) & ") And ([Item] =
'" & Replace([Item],"'","''") & "')") AS RunSum
FROM tblSubProduct
ORDER BY tblSubProduct.QtyID DESC , tblSubProduct.Item DESC;

Sample of report:

Hacksaw
GridHard Pte Ltd
12/03/08 K38455 P3729 $14.00
Iron Ware Pte Ltd
28/02/2008 K29468 P3948 $14.00
24/03/2008 K38571 P3856 $15.00
Total Qty at Hand: 0

(I expected none to be displayed)

When I use subquery:

Record source for the main report:

SELECT tblProduct.InvDate, tblProduct.SerialNo, tblProduct.Item, tblProduct.
Supplier, tblProduct.UnitPrice, tblProduct.InvNo
FROM tblProduct
WHERE (((Exists (SELECT tblSubProduct.QtyID, tblSubProduct.Item,
tblSubProduct.PurchaseQty, tblSubProduct.OutQty, tblSubProduct.PurchaseDate,
DSum(" Nz([PurchaseQty])-Nz([OutQty])","tblSubProduct","(QtyID<=" & NZ([QtyID]
,0) & ") And ([Item] = '" & Replace([Item],"'","''") & "')") AS RunSum
FROM tblSubProduct
WHERE (((DSum(" Nz([PurchaseQty])-Nz([OutQty])","tblSubProduct","(QtyID<=" &
NZ([QtyID],0) & ") And ([Item] = '" & Replace([Item],"'","''") & "')"))>0))
ORDER BY tblSubProduct.QtyID, tblSubProduct.Item)<>False));

Sample of report
Hacksaw
GridHard Pte Ltd
12/03/08 K38455 P3729 $14.00
Iron Ware Pte Ltd
28/02/2008 K29468 P3948 $14.00
24/03/2008 K38571 P3856 $15.00
Total Qty at Hand: 2

I don't understand you subquery mixed with a DSum.

If I understand what you are trying to do, and I am not all
sure I do, I think you can use:

SELECT tblProduct.InvDate, tblProduct.SerialNo,
tblProduct.Item, tblProduct.Supplier,
tblProduct.UnitPrice, tblProduct.InvNo
FROM tblProduct
WHERE (SELECT Sum(Nz(tblSubProduct.PurchaseQty,0) -
Nz(tblSubProduct.OutQty,0))
FROM tblSubProduct
WHERE tblSubProduct.Item = tblProduct.Item) > 0

I am also confused by your use of DSum to generate RunSum
for the subreport. Instead, I would think that all you need
is a running sum text box in the subreport's detail section.
Or, if that was just an attempt to hide things, forget the
whole RunSum thing altogether. If you don't really need to
see the RunSum, the subreport footer total quantity on hand
would be a text box expression like:
=Sum(Nz(PurchaseQty,0) - Nz(OutQty,0))

 

[forcefield via AccessMonster.com]

You are marvellous. I have changed the control source of the text box in my
item footer of my subreport to Sum(Nz(PurchaseQty,0)-Nz(OutQty,0)) and the
main report record source to the query you have suggested and it works
perfectly!
Thank you for your immense help, Marshall.

On my actual tblSubProduct is
PurchaseDate Item PurchaseQty OutQty RunSum

[quoted text clipped - 54 lines]

24/03/2008 K38571 P3856 $15.00
Total Qty at Hand: 2

I don't understand you subquery mixed with a DSum.

If I understand what you are trying to do, and I am not all
sure I do, I think you can use:

SELECT tblProduct.InvDate, tblProduct.SerialNo,
tblProduct.Item, tblProduct.Supplier,
tblProduct.UnitPrice, tblProduct.InvNo
FROM tblProduct
WHERE (SELECT Sum(Nz(tblSubProduct.PurchaseQty,0) -
Nz(tblSubProduct.OutQty,0))
FROM tblSubProduct
WHERE tblSubProduct.Item = tblProduct.Item) > 0

I am also confused by your use of DSum to generate RunSum
for the subreport. Instead, I would think that all you need
is a running sum text box in the subreport's detail section.
Or, if that was just an attempt to hide things, forget the
whole RunSum thing altogether. If you don't really need to
see the RunSum, the subreport footer total quantity on hand
would be a text box expression like:
=Sum(Nz(PurchaseQty,0) - Nz(OutQty,0))

 

[Marshall Barton]

You are marvellous. I have changed the control source of the text box in my
item footer of my subreport to Sum(Nz(PurchaseQty,0)-Nz(OutQty,0)) and the
main report record source to the query you have suggested and it works
perfectly!
Thank you for your immense help, Marshall.

Great. I was just hoping to get close to something that
said we were on the right track, but, as they say, even a
blind pig finds an acorn now and then ;-)