Counting blanks fields for a report

R

Robin Chapple

I have a report where I need to display various levels of member
participation.

Originally I displayed the level including the non active members:

="18 =" & Format((18/(Count([surname]))*100),"#.##") & "%"

which produced the correct result for those conditions.

I have now introduced a calculated field called [Status] where non
active members have an entry. Active members have a blank [Status]
field.

Now that I have the [Status] field I need to count the fields that are
empty to determine active members. This produces a figure still based
on the complete membership. I assume that this is not the way to count
blank fields:

="18 =" & Format((18/(Count([status] Is Null))*100),"#.##") & "%"

Thanks,

Robin Chapple
 
D

Duane Hookom

To count records with a null (no value) in a status field, use
Sum(Abs(IsNull([Status]))
or
Count(*)-Count([Status])
 
R

Robin Chapple

I have not applied this correctly/

I have used each of your suggestions and each gives me the result:
#Div.0!

Here are the complete pieces of code:

="18 =" & Format((18/(Sum(Abs(IsNull([Status])))*100),"#.##") & "%"

="18 =" & Format((18/(Count(*)-Count([Status]))*100),"#.##") & "%"

What have I missed.

Thanks.
 
D

Duane Hookom

You may need to use IIf() to prevent division by 0.

IIf(Sum(IIf([Test1]="y",1,0))=0,0, 18/ Sum(IIf([Test1]="y",1,0))

--
Duane Hookom
MS Access MVP


Robin Chapple said:
I have not applied this correctly/

I have used each of your suggestions and each gives me the result:
#Div.0!

Here are the complete pieces of code:

="18 =" & Format((18/(Sum(Abs(IsNull([Status])))*100),"#.##") & "%"

="18 =" & Format((18/(Count(*)-Count([Status]))*100),"#.##") & "%"

What have I missed.

Thanks.


To count records with a null (no value) in a status field, use
Sum(Abs(IsNull([Status]))
or
Count(*)-Count([Status])
Click to expand...
Click to expand...
 
R

Robin Chapple

I have assumed that I substitute my field name for [Test1] and failed.

This is my complete code:

="18 =" & Format(IIf(Sum(IIf([Status]="y",1,0))=0,0, 18/
Sum(IIf([Status]="y",1,0))),"#.##") & "%"

which does not provide any figures.

Thanks for your patience.

Robin Chapple
 
D

Duane Hookom

What do you mean by "failed"? What do you get if you start small and then
add on:
=Sum(IIf([Status]="y",1,0))
Next change to:
=IIf(Sum(IIf([Status]="y",1,0))=0,0,99999)
Next change to:
=IIf(Sum(IIf([Status]="y",1,0))=0,0,18/Sum(IIf([Status]="y",1,0)) )
Next change to:
=Format(
IIf(Sum(IIf([Status]="y",1,0))=0,0,18/Sum(IIf([Status]="y",1,0)) ),"#.##")
--
Duane Hookom
MS Access MVP
--

Robin Chapple said:
I have assumed that I substitute my field name for [Test1] and failed.

This is my complete code:

="18 =" & Format(IIf(Sum(IIf([Status]="y",1,0))=0,0, 18/
Sum(IIf([Status]="y",1,0))),"#.##") & "%"

which does not provide any figures.

Thanks for your patience.

Robin Chapple

You may need to use IIf() to prevent division by 0.

IIf(Sum(IIf([Test1]="y",1,0))=0,0, 18/ Sum(IIf([Test1]="y",1,0))
Click to expand...
Click to expand...
 
R

Robin Chapple

The first three expressions all show 0.00 and last displays no data at
all.

Robin Chapple
 
D

Duane Hookom

What section is this text box in?
What values do you expect to see? From my perspective, they are all 100%
correct.

--
Duane Hookom
MS Access MVP
--

Robin Chapple said:
The first three expressions all show 0.00 and last displays no data at
all.

Robin Chapple

What do you mean by "failed"? What do you get if you start small and then
add on:
=Sum(IIf([Status]="y",1,0))
Next change to:
=IIf(Sum(IIf([Status]="y",1,0))=0,0,99999)
Next change to:
=IIf(Sum(IIf([Status]="y",1,0))=0,0,18/Sum(IIf([Status]="y",1,0)) )
Next change to:
=Format(
IIf(Sum(IIf([Status]="y",1,0))=0,0,18/Sum(IIf([Status]="y",1,0)) ),"#.##")
Click to expand...
Click to expand...
 
D

Duane Hookom

Can you tell us what you mean by a "blank status field"? Could you please
check the values in the status field again since you seem to have used
either blank or "y".
 
R

Robin Chapple

When I say a blank status I refer to a field that has no data. These
are the active members as shown at the link.

The "y" was introduced by you here:

What do you mean by "failed"? What do you get if you start small and
then
add on:
=Sum(IIf([Status]="y",1,0))
Next change to:
=IIf(Sum(IIf([Status]="y",1,0))=0,0,99999)
Next change to:
=IIf(Sum(IIf([Status]="y",1,0))=0,0,18/Sum(IIf([Status]="y",1,0)) )
Next change to:
=Format(
IIf(Sum(IIf([Status]="y",1,0))=0,0,18/Sum(IIf([Status]="y",1,0))
),"#.##")
--
Duane Hookom
MS Access MVP

Here are the relevany fields from the data:

Number Status
94 Excused
96
216
255
278
284
285
310
417
420
453
471
877
1101
1103
1105
1108
1111
1112
1113
1115
1116
1117 LOA
1119
1151
1153
1155
1156
1157
1158
1164
1165

Can you tell us what you mean by a "blank status field"? Could you please
check the values in the status field again since you seem to have used
either blank or "y".
Click to expand...

Robin Chapple
 
D

Duane Hookom

Ok, lets see if I understand. You want to count the number of records in a
report that have a Null status field. You want to use this count as the
denominator in an expression. You want to format the expression as a
percent.

Try:

="18 =" & Format( IIf( Sum(IsNull([Status])) = 0,0,
1800/Sum(Abs(IsNull([Status])))),"#.##") & "%"


--
Duane Hookom
MS Access MVP


Robin Chapple said:
When I say a blank status I refer to a field that has no data. These
are the active members as shown at the link.

The "y" was introduced by you here:

What do you mean by "failed"? What do you get if you start small and
then
add on:
=Sum(IIf([Status]="y",1,0))
Next change to:
=IIf(Sum(IIf([Status]="y",1,0))=0,0,99999)
Next change to:
=IIf(Sum(IIf([Status]="y",1,0))=0,0,18/Sum(IIf([Status]="y",1,0)) )
Next change to:
=Format(
IIf(Sum(IIf([Status]="y",1,0))=0,0,18/Sum(IIf([Status]="y",1,0))
),"#.##")
--
Duane Hookom
MS Access MVP

Here are the relevany fields from the data:

Number Status
94 Excused
96
216
255
278
284
285
310
417
420
453
471
877
1101
1103
1105
1108
1111
1112
1113
1115
1116
1117 LOA
1119
1151
1153
1155
1156
1157
1158
1164
1165

Can you tell us what you mean by a "blank status field"? Could you please
check the values in the status field again since you seem to have used
either blank or "y".
Click to expand...

Robin Chapple
Click to expand...
 
R

Robin Chapple

Your description is the result that I need.

The code supplies no data:

The field shows: :18 = .%"

In an attempt to prove parts of the expression I entered:

=(Sum(IsNull([Status])))

and the displayed result is: "0.00"


Robin
 
D

Duane Hookom

If that is the result you are getting than there are not records with no
value in the status field. Go back to your data and confirm this.

--
Duane Hookom
MS Access MVP


Robin Chapple said:
Your description is the result that I need.

The code supplies no data:

The field shows: :18 = .%"

In an attempt to prove parts of the expression I entered:

=(Sum(IsNull([Status])))

and the displayed result is: "0.00"


Robin

Ok, lets see if I understand. You want to count the number of records in a
report that have a Null status field. You want to use this count as the
denominator in an expression. You want to format the expression as a
percent.

Try:

="18 =" & Format( IIf( Sum(IsNull([Status])) = 0,0,
1800/Sum(Abs(IsNull([Status])))),"#.##") & "%"
Click to expand...
Click to expand...
 
R

Robin Chapple

The field [Status] is a calculated field from this:

Status: (IIf([ID]=1320,"Excused",IIf([EndLOA]>Date(),"LOA","")))

I am unaware of what the "" will put into the field. Nothing is
visible.

I experimented by adding a field to the query to give us numbers to
count:

Counted: IIf([Status]="LOA",1,0)

Counted: IIf([Status]="Excused",1,0)

Each of the above put a "1" into the field but this

Counted: IIf([Status]="LOA" or "Excused",1,0)

gives all fields as "0"

Is there a symbol for "Any character" ? The asterisk did not work.

Robin
 
D

Duane Hookom

Your status field will never calculate as Null with that expression. Null is
not the same as "".

Try an expression like:
=Sum(Abs(Len(Trim([Status] & "")) = 0) )

--
Duane Hookom
MS Access MVP


Robin Chapple said:
The field [Status] is a calculated field from this:

Status: (IIf([ID]=1320,"Excused",IIf([EndLOA]>Date(),"LOA","")))

I am unaware of what the "" will put into the field. Nothing is
visible.

I experimented by adding a field to the query to give us numbers to
count:

Counted: IIf([Status]="LOA",1,0)

Counted: IIf([Status]="Excused",1,0)

Each of the above put a "1" into the field but this

Counted: IIf([Status]="LOA" or "Excused",1,0)

gives all fields as "0"

Is there a symbol for "Any character" ? The asterisk did not work.

Robin

If that is the result you are getting than there are not records with no
value in the status field. Go back to your data and confirm this.
Click to expand...
Click to expand...
 
R

Robin Chapple

Many thanks Duane,

That gives me a count of active members. I will now attempt to build
it into the calculating expression.

Thanks for your patience.

Robin Chapple
 
R

Robin Chapple

I have failed to incorporate the calculation into my complete
expression.

I will repeat the current position.

I need to calculate the percentage of active members at a variety of
attendance levels for a report.

This expression calculates the active members:

=Sum(Abs(Len(Trim([Status] & "")) = 0) )

This expression calculates what 24 is as a percentage of the TOTAL
membership.

="24 =" & Format((24/(Count([surname]))*100),"#.##") & "%"

I need to substitute the first expression for the "count" part of the
second expression.

Robin Chapple
 
D

Duane Hookom

Try:
="24 =" & Format((24/(Sum(Abs(Len(Trim([Status] & "")) =
0) ) )*100),"#.##") & "%"


--
Duane Hookom
MS Access MVP
--

Robin Chapple said:
I have failed to incorporate the calculation into my complete
expression.

I will repeat the current position.

I need to calculate the percentage of active members at a variety of
attendance levels for a report.

This expression calculates the active members:

=Sum(Abs(Len(Trim([Status] & "")) = 0) )

This expression calculates what 24 is as a percentage of the TOTAL
membership.

="24 =" & Format((24/(Count([surname]))*100),"#.##") & "%"

I need to substitute the first expression for the "count" part of the
second expression.

Robin Chapple


Your status field will never calculate as Null with that expression. Null
is
not the same as "".

Try an expression like:
=Sum(Abs(Len(Trim([Status] & "")) = 0) )
Click to expand...
Click to expand...
 
Top