Suppressing display if COUNT(*) is zero

[Sharon C]

I have a report which lists records that meet a certain
condition. At the bottom, I've added a text field which
counts how many records were returned. But I don't want
this to print anything if no records were returned. I've
tried both of these formulas in ControlSource:

=Count([Status])

=IIf(IsNull(Count(*))," ",Count(*))

They both work fine to provide a count if records are
returned, but when no records are returned, instead of a
blank, I get #Error.

What formula should I use here?

 

[Steve Schapel]

Sharon,

There are a few potential approaches to this. Here are a couple...

1. =DCount("*","YourQueryThatTheReportIsBasedOn")
.... and then use the control's Format property to show nothing if the
value is 0.

2. Put a function like this in a standard module:
Public Function nnz(testvalue As Variant) As Variant
If Not (IsNumeric(testvalue)) Then
nnz = null
Else
nnz = testvalue
End If
End Function
.... and then in your textbox...
=nnz(Count(*))

 

[Sharon C]

Thank you Steve... the DCount and Format solution worked
very well!

-----Original Message-----
Sharon,

There are a few potential approaches to this. Here are a couple...

1. =DCount("*","YourQueryThatTheReportIsBasedOn")
.... and then use the control's Format property to show nothing if the
value is 0.

2. Put a function like this in a standard module:
Public Function nnz(testvalue As Variant) As Variant
If Not (IsNumeric(testvalue)) Then
nnz = null
Else
nnz = testvalue
End If
End Function
.... and then in your textbox...
=nnz(Count(*))

--
Steve Schapel, Microsoft Access MVP

I have a report which lists records that meet a certain
condition. At the bottom, I've added a text field which
counts how many records were returned. But I don't want
this to print anything if no records were returned. I've
tried both of these formulas in ControlSource:

=Count([Status])

=IIf(IsNull(Count(*))," ",Count(*))

They both work fine to provide a count if records are
returned, but when no records are returned, instead of a
blank, I get #Error.

What formula should I use here?

.

 

[Duane Hookom]

=IIf([HasData], Count(*), "")

 

[Sharon C]

Thank you Duane... that worked. Now I have two choices:
DCount or HasData.

You guys are the best!

-----Original Message-----
=IIf([HasData], Count(*), "")

--
Duane Hookom
MS Access MVP

I have a report which lists records that meet a certain
condition. At the bottom, I've added a text field which
counts how many records were returned. But I don't want
this to print anything if no records were returned. I've
tried both of these formulas in ControlSource:

=Count([Status])

=IIf(IsNull(Count(*))," ",Count(*))

They both work fine to provide a count if records are
returned, but when no records are returned, instead of a
blank, I get #Error.

What formula should I use here?

.

 

[Duane Hookom]

HasData is much more efficient. Plus if you open the report with a where
clause, it may not contain any records while the DCount() will return the
number of records in the query, not the report.

--
Duane Hookom
MS Access MVP

Thank you Duane... that worked. Now I have two choices:
DCount or HasData.

You guys are the best!

-----Original Message-----
=IIf([HasData], Count(*), "")

--
Duane Hookom
MS Access MVP

I have a report which lists records that meet a certain
condition. At the bottom, I've added a text field which
counts how many records were returned. But I don't want
this to print anything if no records were returned. I've
tried both of these formulas in ControlSource:

=Count([Status])

=IIf(IsNull(Count(*))," ",Count(*))

They both work fine to provide a count if records are
returned, but when no records are returned, instead of a
blank, I get #Error.

What formula should I use here?

.

 

[Steve Schapel]

Agreed.