Display 0 when Count(*) is null 1

[g3a3n3n3]

Hello

I thought this was a simple thing to do but for some reason nothing work....

I have a textbox on my Report with a simple =count(*) in it which counts the number of times an item is displayed in the report. Now when no items are displayed I need this textbox to show 0.

I've tried all these but none of them work!!!

=IIF(ISNULL(count(*)),0,count(*))
=NZ(count(*))
=IIF(ISNULL(NZ(count(*))),0,count(*))

I've also tried to do the count in one text box and in another textbox show 0 with the help of an IIF statement. But it doesnt work.

I keep getting an #ERROR when I run the report.

I think ideally there should be some kind of ONERROR statement or a ON NODATA but cant seem to figuire it out.

Any help would be appreciated!!

Ta
Gillian

 

[CosmoKramer]

Try:

=IIf(Count(*) Is Null,0,Count(*))

Hoc nomen meum verum non est.

 

[g3a3n3n3]

Still getting the #Error !!!!

 

[hymn]

try in the Report footer
=Abs(Sum([TheNameOfTourField]))


Hope this helps
Hymn

 

[CosmoKramer]

Where is this text box?? It should be in the report footer. If it's in the page footer it won't work, you'll get the #Error.....

Hoc nomen meum verum non est.

 

[g3a3n3n3]

Hymn - That doesn't work!!

CosmoKramer - The textbox is in the Report header and it works perfectly when the count is more than 0. It's only when it's null I get the error. I've tried moving the textbox to the report footer but it didnt make any difference.

Let's try this a different way...
What I have is a report that gives me


Category Count
1 - Apples 4
2 - Grapes 3
3 - Oranges 5
4 - None 7
Total 19
Category Total (Not including 4-None) = 12


This works fine if the values are more than 0. If for example the count for Category 1-3 were 0/null then my report looks like this

Category Count
4 - None 7
Total 7
Category Total (Not including 4-None) = #Error


What I want is

Category Count
4 - None 7
Total 7
Category Total (Not including 4-None) = 0


OR


Category Count
1 - Apples 0
2 - Grapes 0
3 - Oranges 0
4 - None 7
Total 7
Category Total (Not including 4-None) = 0


By the way the last line in my report

Category Total (Not including 4-None) = 0

is actually a subreport included in the main report.
The report is grouped in Categories so

[CategoryName] [Count(*)]

sit in the report header

Thanks for all the help you guys - It's just getting a little frustrating now because I dont think this is a major problem. I know it's a small stuff up somewhere but just cant seem to figure out where.

Thanks again
Gillian

 

[dhookom]

Usually details are in a subreport and totals are in the main report. Your's is the opposite of this. What calculation are you using in the subreport to display the Category Total?

Duane
MS Access MVP

 

[g3a3n3n3]

There are 2 queries
Query 1 gives me all the detailed information which goes into the main report. It groups on the Category field. So the category header contains Category Name and I have put in a textbox which does the count ie =count(*)

Query 2 list only item that satisfy the criteria of "4-None"
In the second report (which becomes the sub report) in group on the category and again I've got a textbox that does another count ie =count(*)

I placed the second report in the footer of the first report to give me the result I wanted. What I need is for the second report count to give me a zero value if there is no data in it.

All help is much appreciated.
Ta
Gillian

 

[CosmoKramer]

Instead of attempting to re-calculate the category total, why don't you subtract the "4 - None" value from the "Total" value to get "Category Total (Not including 4-None)"??

For future posts you may want to include as much information as possible right from the start. There was no mention of a subreport in you original post.....

Hoc nomen meum verum non est.

 

[dhookom]

I don't understand why there is a need for a subreport. Is the formatting or layout different in the subreport? You are just displaying summary counts.
I believe you could use something to count records on the main report or subreport like:
=IIf([HasData],Count(*),0)
The HasData will return true or false while all the Null() attempts will fail.


Duane
MS Access MVP

 

[TonyJollans]

Hi Gillian,

Although I can't properly explain it this should work.

Have one textbox - call it, say, HiddenCount and
- set its ControlSource to =Count(*)
- set its Visible property to No

Have a second textbox - right on top of the first one if you like and
- set its ControlSource to =Iif(IsError(HiddenCount),0,HiddenCount)

Enjoy,
Tony

 

[g3a3n3n3]

Thanks Tony - your solution worked!

CosmoKramer - How do you re calculate a total as you suggested earlier?
Lets say if in my details section I have

CategoryName Count

and in the footer if had the total count

Total Count

The result being


1 - Apples 5
2 - Grapes 4
3 - Oranges 0
4 - None 7
Total 16

What formula should I use in this textbox to calculate Total less None?
Category Total (Not including 4-None) = 9

I'm sorry if these questions seem silly but I'm trying to learn the right way of doing things. Please be patient with me!

Gillian

 

[dhookom]

If CategoryName and CountField are fields in your report's recordsource:
=Abs(Sum([CountField] * ([CategoryName]<>&quot;4-None&quot ) )

Duane
MS Access MVP

 

[g3a3n3n3]

dhookom - that does'nt seem to work and I dont quite understand what you're trying to do here...
In the sum function...
CountField is a Number but CategoryName is a textbox - what are you trying to do with the &quot;*&quot; ?????

 

[CosmoKramer]

Gillian,

To calculate Total less None, try something like this as the control source in the report footer:

=Count(*)-Abs(Sum([CategoryName]="4 - None"))

Hoc nomen meum verum non est.

 

[dhookom]

Are you still using a subreport for one of your category count lines? Are the some or all of the category lines from group headers/footers? Are they all contained in the same section of the report? Is your only field [CategoryName] or are there other relevent fields?

My solution was a method of evaluating CategoryName<>&quot;4-None&quot;. This will return either 0 or -1. As I suggested, if you have a [CountField] that you want to sum, then multiplying that field times either 0 or -1 will actually ignore the &quot;4-None&quot; since the count field is multiplied by 0. Then the expression is summed and then changed to a positive number using Abs().

Duane
MS Access MVP

 

[g3a3n3n3]

Cosmo - Thank you - it works and I managed to learn something new!!

dhookom - I have deleted all subreports and am left with just one very straightforward main report.
The report is grouped by &quot;Category Name&quot; and in this section I have a textbox called &quot;Count&quot; which has =count(*) in it.

In the Header of the report I have another textbox in which I have the formula you have used
=Abs(Sum([Report]!Count*[WorkRelatedIssue]<>&quot;0 - None&quot)

When I run the report a dialog box comes up asking me to enter a value for Report! Count

What am I doing wrong?

Thanks for all your help so far.
Gillian

 

[dhookom]

I thought the field name was CategoryName...
I don't recall providing the [Report]!Count syntax. If you want to count all the records that have the field [WorkRelatedIssue] not equal &quot;0-None&quot; then use:
=Abs( Sum([WorkRelatedIssue] <> &quot;0-None&quot )

To count all the records with WorkRelatedIssue] equal &quot;0-None&quot; then use:
=Abs( Sum([WorkRelatedIssue] = &quot;0-None&quot )

If you had a field [TheMinutes] and wanted sum these where [WorkRelatedIssue] not equal &quot;0-None&quot; then use:
=Abs( Sum([WorkRelatedIssue] <> &quot;0-None&quot; * [TheMinutes]) )

If you wanted to sum [TheMinutes] where [WorkRelatedIssue] equal &quot;0-None&quot; then use:
=Abs( Sum([WorkRelatedIssue] = &quot;0-None&quot; * [TheMinutes]) )





Duane
MS Access MVP

 

[g3a3n3n3]

Thanks Duane that makes sense now