Please help with Count()

The whole report has no data? I thought it was when a box
has no folders.

You can cancel the entire report by using the NoData event:
Cancel = True

If you want to print the report anyway, then the text boxes
the display #Error can be made blank by using an expression
along these lines:
=IIf(IsError(...), Null, ...)
where the ... represents whatever you currently have in the
control source.
--
Marsh
MVP [MS Access]


Please Help wrote:
I just tried changing the code, and it didn't work. When I displayed the
report, I received "#Error" in that box group footer text box. In addition,
the detail section of the report also showed #Error.

I think your original approach was fine, except we just have to figure out
how to handle the report when we have no data/records on the report. When a
report is no data, the report shows "#Error".


"Marshall Barton" wrote:
Change the group footer running sum text box's expression
from =1 to:
=IIf(Count([Box No]) 0, 1, 0)

What is in the control source of the detail text box that
shows #Error ?


Please Help wrote:
I have a small problem regarding the count that you just helped me. It
counts the way I was looking for. However, when a report has no value (in
the Detail section), the count still shows as 1 (in the Report Footer
section). How can I make the count to show 0?

In addition to the Count from above, can you help me how I can make the
report to show a blank when a report contains no value (in the Detail
section)? Currently, the reports shows "#Error" (in the Detail section).


"Marshall Barton" wrote:
Use a text box (named txtRunBoxCount) in the box group
footer section. Set its control source expression to =1 and
its RunningSum property to Over All.

Then the report footer text box can display the number of
boxes by using the expression =txtRunBoxCount


Please Help wrote:
On the report, I have a field for Box No and Folder No. In the detail
section of the report, the records are grouped and counted by boxes. For
example, the number of folders in Box 1 is 4 and etc.

What I have problem with is to have a count formula in the Report Footer
section to count the total number of boxes on the report. I have tried to
use the formula "=count([Box No])" in the Control Source of the text box, and
I got the same total # as the one in the folder count.

Hi Marshall,

Thank you very much for your patience. At the same time, I am sorry the
misunderstanding.

Yes, sometimes, the report may not have any data. When the report has no
data, if possible, I still like the report able to print, and I want to have
a message "No data for matching criteria" printed on the report. At the same
time, I want the count text box in the report footer section to be Null. I
do not care for the "Cancel = True".

When the report has data, it will show data and the count text box in the
report footer section will show the number of boxes on the report. For this
part, we are all set.

We only need the part when the report has no data. I tried to write a
formula "=IIf(IsError(...), Null, ...)" in the ControlSource of each text box
of the data in the Detail Section, and it didn't work.

Can it be done?

Thank you again very much for your help and patience.

"Marshall Barton" wrote:

The whole report has no data? I thought it was when a box
has no folders.

You can cancel the entire report by using the NoData event:
Cancel = True

If you want to print the report anyway, then the text boxes
the display #Error can be made blank by using an expression
along these lines:
=IIf(IsError(...), Null, ...)
where the ... represents whatever you currently have in the
control source.
--
Marsh
MVP [MS Access]


Please Help wrote:
I just tried changing the code, and it didn't work. When I displayed the
report, I received "#Error" in that box group footer text box. In addition,
the detail section of the report also showed #Error.

I think your original approach was fine, except we just have to figure out
how to handle the report when we have no data/records on the report. When a
report is no data, the report shows "#Error".


"Marshall Barton" wrote:
Change the group footer running sum text box's expression
from =1 to:
=IIf(Count([Box No]) 0, 1, 0)

What is in the control source of the detail text box that
shows #Error ?


Please Help wrote:
I have a small problem regarding the count that you just helped me. It
counts the way I was looking for. However, when a report has no value (in
the Detail section), the count still shows as 1 (in the Report Footer
section). How can I make the count to show 0?

In addition to the Count from above, can you help me how I can make the
report to show a blank when a report contains no value (in the Detail
section)? Currently, the reports shows "#Error" (in the Detail section).


"Marshall Barton" wrote:
Use a text box (named txtRunBoxCount) in the box group
footer section. Set its control source expression to =1 and
its RunningSum property to Over All.

Then the report footer text box can display the number of
boxes by using the expression =txtRunBoxCount


Please Help wrote:
On the report, I have a field for Box No and Folder No. In the detail
section of the report, the records are grouped and counted by boxes. For
example, the number of folders in Box 1 is 4 and etc.

What I have problem with is to have a count formula in the Report Footer
section to count the total number of boxes on the report. I have tried to
use the formula "=count([Box No])" in the Control Source of the text box, and
I got the same total # as the one in the folder count.

Please Help wrote:
Yes, sometimes, the report may not have any data. When the report has no
data, if possible, I still like the report able to print, and I want to have
a message "No data for matching criteria" printed on the report. At the same
time, I want the count text box in the report footer section to be Null. I
do not care for the "Cancel = True".

When the report has data, it will show data and the count text box in the
report footer section will show the number of boxes on the report. For this
part, we are all set.

We only need the part when the report has no data. I tried to write a
formula "=IIf(IsError(...), Null, ...)" in the ControlSource of each text box
of the data in the Detail Section, and it didn't work.


Add an invisible label (with the no data text in its
caption) to the report header/footer section

Use the NoData event to make the detail section and/or any
controls you don't want to see invisible:

Me.Section(0).Visible = False
Me.labelNoData.Visible = True
Me.txtTotalFolders.Visible = False

--
Marsh
MVP [MS Access]

Hi Marshall,

As you instructed, I put the following code in the No Data event of the
report.

Me.Section(0).Visible = False
Me.lblNoData.Visible = True
Me.txtTotalFolders.Visible = False

Then I created a label box with the Name "lblNoData" and the Caption "There
are no boxes for the criteria.". I placed the lblNoData box in the Report
Footer section.

When I run the report with "no data", the Detail section of the report still
shows "#Error", and the lblNoData in the Report Footer section shows the
message. The count text box in the Report Footer section disappears.

On the other hand, when I run the report with "data", the Detail section of
the report shows the data, and the lblNoData in the Report Footer section
still shows the message. The count text box in the Report Footer section
appears.

I think I still have problems with the Detail section when there are no
data. When there are data, the lblNoData still shows up. I think these are
two problems that I still have.

Did I miss anything?

Again, thank you very much for your patience in trying to help me. I think
we are almost there.


"Marshall Barton" wrote:

Please Help wrote:
Yes, sometimes, the report may not have any data. When the report has no
data, if possible, I still like the report able to print, and I want to have
a message "No data for matching criteria" printed on the report. At the same
time, I want the count text box in the report footer section to be Null. I
do not care for the "Cancel = True".

When the report has data, it will show data and the count text box in the
report footer section will show the number of boxes on the report. For this
part, we are all set.

We only need the part when the report has no data. I tried to write a
formula "=IIf(IsError(...), Null, ...)" in the ControlSource of each text box
of the data in the Detail Section, and it didn't work.


Add an invisible label (with the no data text in its
caption) to the report header/footer section

Use the NoData event to make the detail section and/or any
controls you don't want to see invisible:

Me.Section(0).Visible = False
Me.labelNoData.Visible = True
Me.txtTotalFolders.Visible = False

--
Marsh
MVP [MS Access]

Hi Marshall,

Below is the code I use in the No Data event of the report. Thanks.

Private Sub Report_NoData(Cancel As Integer)
Me.Section(0).Visible = False
Me.lblNoData.Visible = True
Me.txtCountFolders.Visible = False
Me.txtCountBoxes.Visible = False
End Sub


"Marshall Barton" wrote:

Please Help wrote:
Yes, sometimes, the report may not have any data. When the report has no
data, if possible, I still like the report able to print, and I want to have
a message "No data for matching criteria" printed on the report. At the same
time, I want the count text box in the report footer section to be Null. I
do not care for the "Cancel = True".

When the report has data, it will show data and the count text box in the
report footer section will show the number of boxes on the report. For this
part, we are all set.

We only need the part when the report has no data. I tried to write a
formula "=IIf(IsError(...), Null, ...)" in the ControlSource of each text box
of the data in the Detail Section, and it didn't work.


Add an invisible label (with the no data text in its
caption) to the report header/footer section

Use the NoData event to make the detail section and/or any
controls you don't want to see invisible:

Me.Section(0).Visible = False
Me.labelNoData.Visible = True
Me.txtTotalFolders.Visible = False

--
Marsh
MVP [MS Access]

Set the label's Visible property to No in design view.

I can not imagine a reason why Me.Section(0).Visible = False
isn't doing what it's supposed to. Are you sure you are
seeing the Detail section? Maybe you're seeing a group
header or footer section?? One quick way to identify the
various sections in preview is to set each section's
BackColor to a different color.
--
Marsh
MVP [MS Access]


Please Help wrote:
As you instructed, I put the following code in the No Data event of the
report.

Me.Section(0).Visible = False
Me.lblNoData.Visible = True
Me.txtTotalFolders.Visible = False

Then I created a label box with the Name "lblNoData" and the Caption "There
are no boxes for the criteria.". I placed the lblNoData box in the Report
Footer section.

When I run the report with "no data", the Detail section of the report still
shows "#Error", and the lblNoData in the Report Footer section shows the
message. The count text box in the Report Footer section disappears.

On the other hand, when I run the report with "data", the Detail section of
the report shows the data, and the lblNoData in the Report Footer section
still shows the message. The count text box in the Report Footer section
appears.

I think I still have problems with the Detail section when there are no
data. When there are data, the lblNoData still shows up. I think these are
two problems that I still have.


Please Help wrote:
Yes, sometimes, the report may not have any data. When the report has no
data, if possible, I still like the report able to print, and I want to have
a message "No data for matching criteria" printed on the report. At the same
time, I want the count text box in the report footer section to be Null. I
do not care for the "Cancel = True".

When the report has data, it will show data and the count text box in the
report footer section will show the number of boxes on the report. For this
part, we are all set.

We only need the part when the report has no data. I tried to write a
formula "=IIf(IsError(...), Null, ...)" in the ControlSource of each text box
of the data in the Detail Section, and it didn't work.


"Marshall Barton" wrote:
Add an invisible label (with the no data text in its
caption) to the report header/footer section

Use the NoData event to make the detail section and/or any
controls you don't want to see invisible:

Me.Section(0).Visible = False
Me.labelNoData.Visible = True
Me.txtTotalFolders.Visible = False

Hi Marshall,

I can't thank you enough for your patience in helping me.

You were right. The Detail section was fine. The Group Level 1 Footer (Box
Group Footer) was the one that causing the error. So I added the code
"Me.Section(6).Visible = False" to resolve the issue with Group Level 1
Footer.

Then I changed the Visible property of the label box to "No".

Guess what? After making these two changes, the report turned out the way I
wanted. When there are no data, it only shows the text "No Data". When
there are data, it shows the Detail section and counts.

Again, thank you very much for your help and your patience after going
through such a long journey.

Have a nice weekend!


"Marshall Barton" wrote:

Set the label's Visible property to No in design view.

I can not imagine a reason why Me.Section(0).Visible = False
isn't doing what it's supposed to. Are you sure you are
seeing the Detail section? Maybe you're seeing a group
header or footer section?? One quick way to identify the
various sections in preview is to set each section's
BackColor to a different color.
--
Marsh
MVP [MS Access]


Please Help wrote:
As you instructed, I put the following code in the No Data event of the
report.

Me.Section(0).Visible = False
Me.lblNoData.Visible = True
Me.txtTotalFolders.Visible = False

Then I created a label box with the Name "lblNoData" and the Caption "There
are no boxes for the criteria.". I placed the lblNoData box in the Report
Footer section.

When I run the report with "no data", the Detail section of the report still
shows "#Error", and the lblNoData in the Report Footer section shows the
message. The count text box in the Report Footer section disappears.

On the other hand, when I run the report with "data", the Detail section of
the report shows the data, and the lblNoData in the Report Footer section
still shows the message. The count text box in the Report Footer section
appears.

I think I still have problems with the Detail section when there are no
data. When there are data, the lblNoData still shows up. I think these are
two problems that I still have.


Please Help wrote:
Yes, sometimes, the report may not have any data. When the report has no
data, if possible, I still like the report able to print, and I want to have
a message "No data for matching criteria" printed on the report. At the same
time, I want the count text box in the report footer section to be Null. I
do not care for the "Cancel = True".

When the report has data, it will show data and the count text box in the
report footer section will show the number of boxes on the report. For this
part, we are all set.

We only need the part when the report has no data. I tried to write a
formula "=IIf(IsError(...), Null, ...)" in the ControlSource of each text box
of the data in the Detail Section, and it didn't work.


"Marshall Barton" wrote:
Add an invisible label (with the no data text in its
caption) to the report header/footer section

Use the NoData event to make the detail section and/or any
controls you don't want to see invisible:

Me.Section(0).Visible = False
Me.labelNoData.Visible = True
Me.txtTotalFolders.Visible = False