How can I make a group not to show/display in a report if no data is under that group?

[erick-flores]

Hello,

I have this big report that summary a lot of data. Sometimes a group
need to be shown if that group has data on it otherwire I dont want to
show the group in the report. So, is there an option to not show the
group if no data is under a group?

Any ideas?

Thank You

 

[Marshall Barton]

I have this big report that summary a lot of data. Sometimes a group
need to be shown if that group has data on it otherwire I dont want to
show the group in the report. So, is there an option to not show the
group if no data is under a group?

Add a text box with the expression =Coount(*) to the group
header section. Then use each section's Format event to
check the text box and cancel the section:

Cancel = (thetextbox = 0)

 

[erick-flores]

Add a text box with the expression =Coount(*) to the group
header section. Then use each section's Format event to
check the text box and cancel the section:

Cancel = (thetextbox = 0)

Because I am grouping by different IDs I cant add another
field/expression to my grouping, can I?
I read you reply this to anoher person, and I already check for this
option

Any others suggestions?

 

[Marshall Barton]

Because I am grouping by different IDs I cant add another
field/expression to my grouping, can I?
I read you reply this to anoher person, and I already check for this
option

Any others suggestions?

If it's not a report group, then I think you need to explain
what you mean by "group".

Another thought, how can there be data for the report when a
"group" has no data?

 

[erick-flores]

Ok, let me explain you. Compressors, Stages, Cylinders, Comp. Valve,
Comp. Parts, Gaskets, Piston, Comp. Rod, Nuts and Piston Rings

Compressor has Stage(s) (always)
Stage(s) has Cylinder(s) (always)
Cylinder can have Comp. Valve, Gaskets or Piston
Comp. Valve can have Comp. Parts
Cylinder can has Gasket
Piston can have Comp. Rod or Nuts
Nuts can have Piston Rings

So this si kind of the idea. I want to see my report first. group by:
1. Compressor (cmp_id)
2. Stages (stg_id)
3. Cylinders (cyl_id)
4. and then all the other components from the cylinders.

What I am doing:
1st group cmp_id
2nd group stg_id
3rd group cyl_id
4th group Comp Valve
5th group Comp. Parts
..
..
..
So I was thinking if a Cylinder only has a Gasket then only display the
gasket group.

I think there should be a better way of doing this, but I dont know

 

[erick-flores]

Also when there is no data under a group, the others groups will not
work. So the hold report will display no data. It will only show the
group headers. How can I fix this? any ideas?

 

[Marshall Barton]

Ok, let me explain you. Compressors, Stages, Cylinders, Comp. Valve,
Comp. Parts, Gaskets, Piston, Comp. Rod, Nuts and Piston Rings

Compressor has Stage(s) (always)
Stage(s) has Cylinder(s) (always)
Cylinder can have Comp. Valve, Gaskets or Piston
Comp. Valve can have Comp. Parts
Cylinder can has Gasket
Piston can have Comp. Rod or Nuts
Nuts can have Piston Rings

So this si kind of the idea. I want to see my report first. group by:
1. Compressor (cmp_id)
2. Stages (stg_id)
3. Cylinders (cyl_id)
4. and then all the other components from the cylinders.

What I am doing:
1st group cmp_id
2nd group stg_id
3rd group cyl_id
4th group Comp Valve
5th group Comp. Parts
.
.
.
So I was thinking if a Cylinder only has a Gasket then only display the
gasket group.

I think there should be a better way of doing this, but I dont know

That seems like a good way to do it.

As far as I can tell from your description, I still think
you can use a text box named txtValveCount in the Valve
group's header section with the expression =Count(*)
Then the section can be suppressed by adding a line of code
to its Format event:
Cancel = (Me.txtValveCount = 0)
Repeat the same kind of thing in each group header.

If I am still misunderstanding what you are saying, please
post a Copy/Paste of the report's record source query along
with some explanation of how the fields are used in the
report.

 

[erick-flores]

As far as I can tell from your description, I still think

you can use a text box named txtValveCount in the Valve
group's header section with the expression =Count(*)
Then the section can be suppressed by adding a line of code
to its Format event:
Cancel = (Me.txtValveCount = 0)
Repeat the same kind of thing in each group header.

I think I dont quite understand what you mean, if I go to Sorting and
Grouping in my report, under the Field/Expression colunm I have:
cmp_id
stg_id
cyl_id
etc
So the header of my group shows: cmp_id Header, stg_id Header, etc
Do you mean add in that Field/Expression "=Count(*)"? So the name of
the group would be "Count(*) Header" is that what you mean. And the
txtValveCount, I cant add a textbox to the header, it wouldnt let me.
Maybe I am getting the wrong idea, perhaps I can use the txtValveCount.
If you can please guide me step by step I will really appreciatted

Thank you

 

[Marshall Barton]

I think I dont quite understand what you mean, if I go to Sorting and
Grouping in my report, under the Field/Expression colunm I have:
cmp_id
stg_id
cyl_id
etc
So the header of my group shows: cmp_id Header, stg_id Header, etc
Do you mean add in that Field/Expression "=Count(*)"? So the name of
the group would be "Count(*) Header" is that what you mean. And the
txtValveCount, I cant add a textbox to the header, it wouldnt let me.
Maybe I am getting the wrong idea, perhaps I can use the txtValveCount.

No, no, not that at all.

Add a text box to the Valve group header. Double click on
this new text box to bring up its property sheet. Find the
Name property and type an appropriate name (e.g.
txtValveCount). Find the ControlSource property and type
=Count(*)
Now find the OnFormat property and select [Event Procedure]
from the drop down list. Then click on the builder button
in the right margin (the button with three dots). That
should take you to the headersection's Format event
procedure. Then just type the line:
Cancel = (Me.txtValveCount = 0)
into the procedure.

Test the report to see if the Valve section does what you
want when there are no valves. If it does, repeat the above
process for each of the other group header sections.

 

[erick-flores]

Add a text box to the Valve group header.
I add a text box to the fields under the Valve group header name:
gskCount. But not in the "group header"
When you say "Add a text box to the Valve group header" that for me is
create/add a text box inside the group header, where right now the
field is gsk_id. So I dont see how I can add a text box to the group
header. So I add it under the fields under my group header.

It did work when there is data under that group. I can see the textbox
counting the data. But when there is no data an error will occur.

"Run-time error '2427':
You entered an expression that has no value."
And it will highlight the "Cancel = (Me.gskCount = 0)"

My code behind the OnFormat is:
Private Sub GroupHeader6_Format(Cancel As Integer, FormatCount As
Integer)
Cancel = (Me.gskCount = 0)
End Sub

I am doing something wrong?

Thank you

 

[Marshall Barton]

I add a text box to the fields under the Valve group header name:
gskCount. But not in the "group header"
When you say "Add a text box to the Valve group header" that for me is
create/add a text box inside the group header, where right now the
field is gsk_id. So I dont see how I can add a text box to the group
header. So I add it under the fields under my group header.

It did work when there is data under that group. I can see the textbox
counting the data. But when there is no data an error will occur.

"Run-time error '2427':
You entered an expression that has no value."
And it will highlight the "Cancel = (Me.gskCount = 0)"

My code behind the OnFormat is:
Private Sub GroupHeader6_Format(Cancel As Integer, FormatCount As
Integer)
Cancel = (Me.gskCount = 0)
End Sub

I am doing something wrong?

It sounds like you did not put the gskCount text box in the
group header that you want to count (GroupHeader6 ?).
Adding the text box to the header section is the same as
addin one to any other section. I don't understand why you
are having trouble doing it.

 

[erick-flores]

I cant get it to work...but thanks anyways

Do you know any other way to do this?

 

[Marshall Barton]

I cant get it to work...but thanks anyways

Do you know any other way to do this?

That IS the way to do this. Maybe we should explore why it
isn't working for you? Can't you just click on the text box
button on the toolbox toolbar and then drag the outline of
the text box in the header section? Isn't that what you did
to add text boxes to the other sections?

 

[erick-flores]

That IS the way to do this. Maybe we should explore why it
isn't working for you? Can't you just click on the text box
button on the toolbox toolbar and then drag the outline of
the text box in the header section? Isn't that what you did
to add text boxes to the other sections?

Yes, and I complete understand what you mean BUT I dont see how to drag
the textbox to the "header" like where right now says "cmp_id Header"
It wont let me. I can put it UNDER the header section but not in the
title that sasys "cmp_id" and has an arrow. Maybe I am not doing the
right thing grouping?

Sorry for my misunderstanding and thanks for your support, I am going
crazy with this problem

 

[erick-flores]

This is what my report design looks like:

-----------------------------------------------------------------------------------------------------------
^cmp_id Header==========================================
Tag Num:
Description:
Notes:

^stg_id Header===========================================
Description:
Cyl Num:
Notes:

^cyl_id Header===========================================
Description:
cyl Num:
Material:
Components Material
Part #
-----------------------------------------------------------------------------------------------------------
^cva_id Header===========================================
cva.Description cva.material
cva.part#

^gsk_id Header==================================== <-----(1)Here is
where you want me to insert the texbox, right???? or
gsk.Description gsk.material
gsk.part#
(2)txtgsk_Count:<--------------Here?

I tried (1) and it did not let me put a textbox in there, then I tried
(2) and it worked when there is data for that group, it actually counts
every record. But when there is no data I got the environment I already
told you...the error message.

So here is an outline of my report, it has more and more groups after
the cyl_id. The principal groups are: cmp_id, stg_id and cyl_id all
three are shown every page. Then the cylinder can have multiple
components. So my plan is create a group for each one, and display it
if the cylinder has that component.

Hope this can help a lil more, Thanks

 

[erick-flores]

-----------------------------------------------------------------------------------------------------------

^cmp_id Header==========================================
Tag Num:
Description:
Notes:

Also do you know how to change the arrow that points up in the group
headers?

Because I want the detail header for all my groups.

(arrow point down) cmp_id Header================================
this is by default

What I want:
(arrow point up) cmp_id Header==================================

So my report will look something like:
--------------------------------------------------------------------------------------------------------------<--top
cmp_description
(^)[<--arrow point up] cmp_id Header==============================

stg_description
^ stg_id Header=============================================

cyl_description
^ cyl_id Header=============================================

and so on...

Do you know how to change that option for the group headers?

 

[erick-flores]

Forgot to add the last Group: Detail, so its clear

-----------------------------------------------------------------------------------------------------------
^cmp_id Header==========================================
Tag Num:
Description:
Notes:

Also do you know how to change the arrow that points up in the group
headers?

Because I want the detail header for all my groups.

(arrow point down) cmp_id Header================================
this is by default

What I want:
(arrow point up) cmp_id Header==================================

So my report will look something like:
--------------------------------------------------------------------------------------------------------------<--top
cmp_description
(^)[<--arrow point up] cmp_id Header==============================

stg_description
^ stg_id Header=============================================

cyl_description
^ cyl_id Header=============================================

and so on...
^ Detail===================================================
Do you know how to change that option for the group headers?

 

[Marshall Barton]

This is what my report design looks like:

-----------------------------------------------------------------------------------------------------------
^cmp_id Header==========================================
Tag Num:
Description:
Notes:

^stg_id Header===========================================
Description:
Cyl Num:
Notes:

^cyl_id Header===========================================
Description:
cyl Num:
Material:
Components Material
Part #
-----------------------------------------------------------------------------------------------------------
^cva_id Header===========================================
cva.Description cva.material
cva.part#

^gsk_id Header==================================== <-----(1)Here is
where you want me to insert the texbox, right???? or
gsk.Description gsk.material
gsk.part#
(2)txtgsk_Count:<--------------Here?

I tried (1) and it did not let me put a textbox in there, then I tried
(2) and it worked when there is data for that group, it actually counts
every record. But when there is no data I got the environment I already
told you...the error message.

So here is an outline of my report, it has more and more groups after
the cyl_id. The principal groups are: cmp_id, stg_id and cyl_id all
three are shown every page. Then the cylinder can have multiple
components. So my plan is create a group for each one, and display it
if the cylinder has that component.

No, you can not place controls on the divider bar above the
top of the section. Putting it in the section directly
below the bar is the right place.

I don't understand how a reference to the =Count(*) text box
can produce that error. If there are no details in the
group, then the value of the text box should be 0. You
should not be getting some kind of non-existent value
message. The only way I can think of getting that message
is when you are referring to a control in a subreport that
has no data or if the code is in the report's Open event
instead of the group header section's Format event.

Don't get carried away with too many groups. There is a
limit of 10 grouping levels.

 

[erick-flores]

Well I guess I am going to need some coding for my report. What I am
trying to do is a replica of a FoxPro application. This application
produces the same report that I am trying to create. This is what the
groups headers looks like:
^ Page Header
Tag Num
Business Team
^ Group Header 1: cmp_id===================================

Description
^ Group Header 2: stg_id====================================

Description
Components Material
Part#
^ Groups Header 3: cyl_id===================================

Under this group are all the different components that the cylinders
can have. They are all one on top of another, like you can see one line
of data, but if you start moving the texbox you can see that there are
5 texbox one of top of another. So I guess that there is some code
behind this group so I will print only if there is data behind a
particular component
^ Group Header 4: bom.type1 + STR(bom.id1)

Same thing for these group, but this group will show the components
under the cylinder components.
^ Group Header 5: bom.type2 + STR(bom.id2)

Here are the last components under the components from the cylinder
components
^ Detail
^ Group Footer 5: bom.type2 + STR (bom.id2)
^ Group Footer 4: bom.type1 + STR (bom.id1)
^ Group Footer 3: cyl_id
^ Group Footer 2: stg_id
^ Group Footer 1: cmp_id

^ Pafe Footer

I was trying to imitate this report in Access. But now that I know
Access dont more than 10 groups, I guess I need to put some code behind
my Groups (4,5 and Detail). Also if you notice all the arrows for the
groups are pointing up. In Access is the other way, the arrows for the
groups point down.

Do you have any ideas of how to create some code for my groups in
Access? So I can see my report the same as I see it in FoxPro? or
perhaps another complete different way, that will produce the
same/similar results?

Thank you

 

[erick-flores]

I got the answer about the arrows in the groups. Now, I know that does
not matter.