Please help with Count()

[Please Help]

Good morning,

I am creating a report of inventory of boxes. In each box, we have folders.
The report will show a record for each folder, instead of a record for each
box.

In the Report Footer section of the report, I have two text boxes. One is
to count the number of folders on the report, and the other one is to count
the number of boxes on the report.

Assuming that there are 3 folders in each box and the report shows 3 boxes.
The folder count text box should show 9 folders, and the box count text box
should show 3 boxes.

I have no problem with the count on the folders, but I have problem with the
formula on counting the boxes. My box count text box shows 9 boxes, instead
of 3 (based on the example above).

Please help.

Thanks.

 

[Marshall Barton]

I am creating a report of inventory of boxes. In each box, we have folders.
The report will show a record for each folder, instead of a record for each
box.

In the Report Footer section of the report, I have two text boxes. One is
to count the number of folders on the report, and the other one is to count
the number of boxes on the report.

Assuming that there are 3 folders in each box and the report shows 3 boxes.
The folder count text box should show 9 folders, and the box count text box
should show 3 boxes.

I have no problem with the count on the folders, but I have problem with the
formula on counting the boxes. My box count text box shows 9 boxes, instead
of 3 (based on the example above).

If there is always 3 folders per box, why not just divide
the number of folders by 3?

=foldercount \ 3 - (3 * (foldercount \ 3) < foldercount)

If that's inadequate, then please explain more about how the
report organizes the folders and boxes.

 

[Michael]

Will you give some code/properties settings so we know how you are trying to
set the property and/or code the property?

 

[Please Help]

Hi Marshall,

Thanks for your response. Unfortunately, there will not always be 3 folders
in each box. Depending on the size of the folders, the number of folders in
each box can be varied.

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.

For the Folders count text box, I use the formula "=count([Folder No])", and
I get a correct # of folders on the report.

Thanks.

 

[Please Help]

Hi Michael,

Thanks for your response. If you don't mind, please see my response to
Marshall Barton below.

Thanks.

 

[Marshall Barton]

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
--
Marsh
MVP [MS Access]

Thanks for your response. Unfortunately, there will not always be 3 folders
in each box. Depending on the size of the folders, the number of folders in
each box can be varied.

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.

For the Folders count text box, I use the formula "=count([Folder No])", and
I get a correct # of folders on the report.

If there is always 3 folders per box, why not just divide
the number of folders by 3?

=foldercount \ 3 - (3 * (foldercount \ 3) < foldercount)

If that's inadequate, then please explain more about how the
report organizes the folders and boxes.

 

[Please Help]

Marshall,

You are genius. It works.

Thank you very much.

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
--
Marsh
MVP [MS Access]

Thanks for your response. Unfortunately, there will not always be 3 folders
in each box. Depending on the size of the folders, the number of folders in
each box can be varied.

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.

For the Folders count text box, I use the formula "=count([Folder No])", and
I get a correct # of folders on the report.

Please Help wrote:
I am creating a report of inventory of boxes. In each box, we have folders.
The report will show a record for each folder, instead of a record for each
box.

In the Report Footer section of the report, I have two text boxes. One is
to count the number of folders on the report, and the other one is to count
the number of boxes on the report.

Assuming that there are 3 folders in each box and the report shows 3 boxes.
The folder count text box should show 9 folders, and the box count text box
should show 3 boxes.

I have no problem with the count on the folders, but I have problem with the
formula on counting the boxes. My box count text box shows 9 boxes, instead
of 3 (based on the example above).


If there is always 3 folders per box, why not just divide
the number of folders by 3?

=foldercount \ 3 - (3 * (foldercount \ 3) < foldercount)

If that's inadequate, then please explain more about how the
report organizes the folders and boxes.

 

[Please Help]

Good morning Marshall,

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).

Thanks.

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
--
Marsh
MVP [MS Access]

Thanks for your response. Unfortunately, there will not always be 3 folders
in each box. Depending on the size of the folders, the number of folders in
each box can be varied.

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.

For the Folders count text box, I use the formula "=count([Folder No])", and
I get a correct # of folders on the report.

Please Help wrote:
I am creating a report of inventory of boxes. In each box, we have folders.
The report will show a record for each folder, instead of a record for each
box.

In the Report Footer section of the report, I have two text boxes. One is
to count the number of folders on the report, and the other one is to count
the number of boxes on the report.

Assuming that there are 3 folders in each box and the report shows 3 boxes.
The folder count text box should show 9 folders, and the box count text box
should show 3 boxes.

I have no problem with the count on the folders, but I have problem with the
formula on counting the boxes. My box count text box shows 9 boxes, instead
of 3 (based on the example above).


If there is always 3 folders per box, why not just divide
the number of folders by 3?

=foldercount \ 3 - (3 * (foldercount \ 3) < foldercount)

If that's inadequate, then please explain more about how the
report organizes the folders and boxes.

 

[Marshall Barton]

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 ?
--
Marsh
MVP [MS Access]

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).

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

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]

Hi Marshall,

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".

Thanks.

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 ?
--
Marsh
MVP [MS Access]

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).

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.

 

[Marshall Barton]

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]

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".

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 ?

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).


:
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]

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.

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]

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".

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).


:
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.

 

[Marshall Barton]

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

 

[Please Help]

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.

 

[Please Help]

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]

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.

 

[Please Help]

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!

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]

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.