Suppressing display if COUNT(*) is zero

  • Thread starter Thread starter Sharon C
  • Start date Start date
S

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?
 
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(*))
 
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


Sharon said:
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?
Click to expand...
.
Click to expand...
 
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?
Click to expand...


.
Click to expand...
 
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


Sharon C said:
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?
Click to expand...


.
Click to expand...
Click to expand...
 
Back
Top