Hide detail if only one record

I am trying to have a report NOT show a detail if there is only one record in
a group. I would like to only see the group total since there is only one
record. My grouping works fine, but a single record group is taking up too
much room on the report. Does anyone have any idea how to accomplish this?

Create a group header and add a text box:
Name: txtGroupCount
Control Source: =Count(*)
Visible: No
Add code to the details section On Format event:
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Me.Detail.Visible = Me.txtCountGroup 1
End Sub


--
Duane Hookom
MS Access MVP


"Neal" wrote in message
...
I am trying to have a report NOT show a detail if there is only one record
in
a group. I would like to only see the group total since there is only one
record. My grouping works fine, but a single record group is taking up
too
much room on the report. Does anyone have any idea how to accomplish
this?

I am trying to get this solution to work, but have not yet been successful.
I am trying to hide a Detail section, and the count keeps returning 1 whether
there are 0, 1, or more records in the detail section. Any idea what I might
be doing wrong?


"Duane Hookom" wrote:

Create a group header and add a text box:
Name: txtGroupCount
Control Source: =Count(*)
Visible: No
Add code to the details section On Format event:
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Me.Detail.Visible = Me.txtCountGroup 1
End Sub


--
Duane Hookom
MS Access MVP


"Neal" wrote in message
...
I am trying to have a report NOT show a detail if there is only one record
in
a group. I would like to only see the group total since there is only one
record. My grouping works fine, but a single record group is taking up
too
much room on the report. Does anyone have any idea how to accomplish
this?

The control needs to be added to the detail section not the group header or
footer.
Name: txtGroupCount
Control Source: =1
Running Sum: Over Group
Visible: No

Add the code to the Detail's format event as Duane originally posted.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

"AccessMan" wrote in message
...
I am trying to get this solution to work, but have not yet been successful.
I am trying to hide a Detail section, and the count keeps returning 1
whether
there are 0, 1, or more records in the detail section. Any idea what I
might
be doing wrong?


"Duane Hookom" wrote:

Create a group header and add a text box:
Name: txtGroupCount
Control Source: =Count(*)
Visible: No
Add code to the details section On Format event:
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Me.Detail.Visible = Me.txtCountGroup 1
End Sub


--
Duane Hookom
MS Access MVP


"Neal" wrote in message
...
I am trying to have a report NOT show a detail if there is only one
record
in
a group. I would like to only see the group total since there is only
one
record. My grouping works fine, but a single record group is taking up
too
much room on the report. Does anyone have any idea how to accomplish
this?

Thanks John and Duane, that works. I have a wrinkle that I'd like to apply
though. I'd like the detail section to not appear at all if there are no
related records. Unfortunately, txtGroupCount yields a value of 1 even when
there are no related records. Setting 'Can Shrink' to Yes doesn't work.

"John Spencer" wrote:

The control needs to be added to the detail section not the group header or
footer.
Name: txtGroupCount
Control Source: =1
Running Sum: Over Group
Visible: No

Add the code to the Detail's format event as Duane originally posted.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

"AccessMan" wrote in message
...
I am trying to get this solution to work, but have not yet been successful.
I am trying to hide a Detail section, and the count keeps returning 1
whether
there are 0, 1, or more records in the detail section. Any idea what I
might
be doing wrong?


"Duane Hookom" wrote:

Create a group header and add a text box:
Name: txtGroupCount
Control Source: =Count(*)
Visible: No
Add code to the details section On Format event:
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Me.Detail.Visible = Me.txtCountGroup 1
End Sub


--
Duane Hookom
MS Access MVP


"Neal" wrote in message
...
I am trying to have a report NOT show a detail if there is only one
record
in
a group. I would like to only see the group total since there is only
one
record. My grouping works fine, but a single record group is taking up
too
much room on the report. Does anyone have any idea how to accomplish
this?

That makes no sense to me. If there are no records, then there would be no
detail section at all and also there should be no group. Obviously I don't
understand something about your report.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

"AccessMan" wrote in message
...
Thanks John and Duane, that works. I have a wrinkle that I'd like to
apply
though. I'd like the detail section to not appear at all if there are no
related records. Unfortunately, txtGroupCount yields a value of 1 even
when
there are no related records. Setting 'Can Shrink' to Yes doesn't work.

"John Spencer" wrote:

The control needs to be added to the detail section not the group header
or
footer.
Name: txtGroupCount
Control Source: =1
Running Sum: Over Group
Visible: No

Add the code to the Detail's format event as Duane originally posted.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

"AccessMan" wrote in message
...
I am trying to get this solution to work, but have not yet been
successful.
I am trying to hide a Detail section, and the count keeps returning 1
whether
there are 0, 1, or more records in the detail section. Any idea what I
might
be doing wrong?


"Duane Hookom" wrote:

Create a group header and add a text box:
Name: txtGroupCount
Control Source: =Count(*)
Visible: No
Add code to the details section On Format event:
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Me.Detail.Visible = Me.txtCountGroup 1
End Sub


--
Duane Hookom
MS Access MVP


"Neal" wrote in message
...
I am trying to have a report NOT show a detail if there is only one
record
in
a group. I would like to only see the group total since there is
only
one
record. My grouping works fine, but a single record group is taking
up
too
much room on the report. Does anyone have any idea how to
accomplish
this?

Sorry. For the sake of discussion, let's say that I have a set of tables for
tracking married couples' names and the children they have, if any. I use a
query with a left outer join to select ALL married couples and whatever
children they have. For those married couples that have no children, the
query returns non-null values for the married couple names fields and null
values for the children's names fields. I use a report with one grouping
field to display the married couple data, and the detail section for the
children data. As things stand now, I get the field labels and null values
for the children when the couple has no children. I'd like to fully suppress
the detail section when there are no children.

"John Spencer" wrote:

That makes no sense to me. If there are no records, then there would be no
detail section at all and also there should be no group. Obviously I don't
understand something about your report.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

"AccessMan" wrote in message
...
Thanks John and Duane, that works. I have a wrinkle that I'd like to
apply
though. I'd like the detail section to not appear at all if there are no
related records. Unfortunately, txtGroupCount yields a value of 1 even
when
there are no related records. Setting 'Can Shrink' to Yes doesn't work.

"John Spencer" wrote:

The control needs to be added to the detail section not the group header
or
footer.
Name: txtGroupCount
Control Source: =1
Running Sum: Over Group
Visible: No

Add the code to the Detail's format event as Duane originally posted.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

"AccessMan" wrote in message
...
I am trying to get this solution to work, but have not yet been
successful.
I am trying to hide a Detail section, and the count keeps returning 1
whether
there are 0, 1, or more records in the detail section. Any idea what I
might
be doing wrong?


"Duane Hookom" wrote:

Create a group header and add a text box:
Name: txtGroupCount
Control Source: =Count(*)
Visible: No
Add code to the details section On Format event:
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Me.Detail.Visible = Me.txtCountGroup 1
End Sub


--
Duane Hookom
MS Access MVP


"Neal" wrote in message
...
I am trying to have a report NOT show a detail if there is only one
record
in
a group. I would like to only see the group total since there is
only
one
record. My grouping works fine, but a single record group is taking
up
too
much room on the report. Does anyone have any idea how to
accomplish
this?

If there is one record then the lines below should hide the detail
section completely.

Me.Detail.Visible = (Me.txtCountGroup 1)

If you want to hide labels in the group header, then you need something
like the following in the format event of the group.

Me.ChildNameLabel.Visible = (Len(Trim(Me.ChildName & "")) 0)
Me.ChildDOBLabel.Visible = ((Len(Trim(Me.ChildDOB & "")) 0)


'================================================= ===
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'================================================= ===


AccessMan wrote:
Sorry. For the sake of discussion, let's say that I have a set of tables for
tracking married couples' names and the children they have, if any. I use a
query with a left outer join to select ALL married couples and whatever
children they have. For those married couples that have no children, the
query returns non-null values for the married couple names fields and null
values for the children's names fields. I use a report with one grouping
field to display the married couple data, and the detail section for the
children data. As things stand now, I get the field labels and null values
for the children when the couple has no children. I'd like to fully suppress
the detail section when there are no children.

"John Spencer" wrote:

That makes no sense to me. If there are no records, then there would be no
detail section at all and also there should be no group. Obviously I don't
understand something about your report.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

"AccessMan" wrote in message
...
Thanks John and Duane, that works. I have a wrinkle that I'd like to
apply
though. I'd like the detail section to not appear at all if there are no
related records. Unfortunately, txtGroupCount yields a value of 1 even
when
there are no related records. Setting 'Can Shrink' to Yes doesn't work.

"John Spencer" wrote:

The control needs to be added to the detail section not the group header
or
footer.
Name: txtGroupCount
Control Source: =1
Running Sum: Over Group
Visible: No

Add the code to the Detail's format event as Duane originally posted.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

"AccessMan" wrote in message
...
I am trying to get this solution to work, but have not yet been
successful.
I am trying to hide a Detail section, and the count keeps returning 1
whether
there are 0, 1, or more records in the detail section. Any idea what I
might
be doing wrong?


"Duane Hookom" wrote:

Create a group header and add a text box:
Name: txtGroupCount
Control Source: =Count(*)
Visible: No
Add code to the details section On Format event:
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Me.Detail.Visible = Me.txtCountGroup 1
End Sub


--
Duane Hookom
MS Access MVP


"Neal" wrote in message
...
I am trying to have a report NOT show a detail if there is only one
record
in
a group. I would like to only see the group total since there is
only
one
record. My grouping works fine, but a single record group is taking
up
too
much room on the report. Does anyone have any idea how to
accomplish
this?

This did not work when I tried it a few days ago. I displayed txtCountGroup
as part of debugging, and its value was 1 whenever there were null records
for display. Aside from that, the displayed value reflected the true number
of records for display in the detail section. I will create a new, simple
report and repeat the solution attempt, and I'll report back.

Thanks!

"John Spencer" wrote:

If there is one record then the lines below should hide the detail
section completely.

Me.Detail.Visible = (Me.txtCountGroup 1)

If you want to hide labels in the group header, then you need something
like the following in the format event of the group.

Me.ChildNameLabel.Visible = (Len(Trim(Me.ChildName & "")) 0)
Me.ChildDOBLabel.Visible = ((Len(Trim(Me.ChildDOB & "")) 0)


'================================================= ===
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'================================================= ===


AccessMan wrote:
Sorry. For the sake of discussion, let's say that I have a set of tables for
tracking married couples' names and the children they have, if any. I use a
query with a left outer join to select ALL married couples and whatever
children they have. For those married couples that have no children, the
query returns non-null values for the married couple names fields and null
values for the children's names fields. I use a report with one grouping
field to display the married couple data, and the detail section for the
children data. As things stand now, I get the field labels and null values
for the children when the couple has no children. I'd like to fully suppress
the detail section when there are no children.

"John Spencer" wrote:

That makes no sense to me. If there are no records, then there would be no
detail section at all and also there should be no group. Obviously I don't
understand something about your report.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

"AccessMan" wrote in message
...
Thanks John and Duane, that works. I have a wrinkle that I'd like to
apply
though. I'd like the detail section to not appear at all if there are no
related records. Unfortunately, txtGroupCount yields a value of 1 even
when
there are no related records. Setting 'Can Shrink' to Yes doesn't work.

"John Spencer" wrote:

The control needs to be added to the detail section not the group header
or
footer.
Name: txtGroupCount
Control Source: =1
Running Sum: Over Group
Visible: No

Add the code to the Detail's format event as Duane originally posted.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

"AccessMan" wrote in message
...
I am trying to get this solution to work, but have not yet been
successful.
I am trying to hide a Detail section, and the count keeps returning 1
whether
there are 0, 1, or more records in the detail section. Any idea what I
might
be doing wrong?


"Duane Hookom" wrote:

Create a group header and add a text box:
Name: txtGroupCount
Control Source: =Count(*)
Visible: No
Add code to the details section On Format event:
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Me.Detail.Visible = Me.txtCountGroup 1
End Sub


--
Duane Hookom
MS Access MVP


"Neal" wrote in message
...
I am trying to have a report NOT show a detail if there is only one
record
in
a group. I would like to only see the group total since there is
only
one
record. My grouping works fine, but a single record group is taking
up
too
much room on the report. Does anyone have any idea how to
accomplish
this?

I got the same results with a new, bare bones set of database objects and
data. The counter returns a value of 1 when there is no data to be shown in
the detail section of the report.

"AccessMan" wrote:

This did not work when I tried it a few days ago. I displayed txtCountGroup
as part of debugging, and its value was 1 whenever there were null records
for display. Aside from that, the displayed value reflected the true number
of records for display in the detail section. I will create a new, simple
report and repeat the solution attempt, and I'll report back.

Thanks!

"John Spencer" wrote:

If there is one record then the lines below should hide the detail
section completely.

Me.Detail.Visible = (Me.txtCountGroup 1)

If you want to hide labels in the group header, then you need something
like the following in the format event of the group.

Me.ChildNameLabel.Visible = (Len(Trim(Me.ChildName & "")) 0)
Me.ChildDOBLabel.Visible = ((Len(Trim(Me.ChildDOB & "")) 0)


'================================================= ===
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'================================================= ===


AccessMan wrote:
Sorry. For the sake of discussion, let's say that I have a set of tables for
tracking married couples' names and the children they have, if any. I use a
query with a left outer join to select ALL married couples and whatever
children they have. For those married couples that have no children, the
query returns non-null values for the married couple names fields and null
values for the children's names fields. I use a report with one grouping
field to display the married couple data, and the detail section for the
children data. As things stand now, I get the field labels and null values
for the children when the couple has no children. I'd like to fully suppress
the detail section when there are no children.

"John Spencer" wrote:

That makes no sense to me. If there are no records, then there would be no
detail section at all and also there should be no group. Obviously I don't
understand something about your report.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

"AccessMan" wrote in message
...
Thanks John and Duane, that works. I have a wrinkle that I'd like to
apply
though. I'd like the detail section to not appear at all if there are no
related records. Unfortunately, txtGroupCount yields a value of 1 even
when
there are no related records. Setting 'Can Shrink' to Yes doesn't work.

"John Spencer" wrote:

The control needs to be added to the detail section not the group header
or
footer.
Name: txtGroupCount
Control Source: =1
Running Sum: Over Group
Visible: No

Add the code to the Detail's format event as Duane originally posted.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

"AccessMan" wrote in message
...
I am trying to get this solution to work, but have not yet been
successful.
I am trying to hide a Detail section, and the count keeps returning 1
whether
there are 0, 1, or more records in the detail section. Any idea what I
might
be doing wrong?


"Duane Hookom" wrote:

Create a group header and add a text box:
Name: txtGroupCount
Control Source: =Count(*)
Visible: No
Add code to the details section On Format event:
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Me.Detail.Visible = Me.txtCountGroup 1
End Sub


--
Duane Hookom
MS Access MVP


"Neal" wrote in message
...
I am trying to have a report NOT show a detail if there is only one
record
in
a group. I would like to only see the group total since there is
only
one
record. My grouping works fine, but a single record group is taking
up
too
much room on the report. Does anyone have any idea how to
accomplish
this?