Return a Zero with Count Function

[magmike]

I have an unbound field on a search form titled SponsorCount -

=Count([SPONNAME])

When a search is completed, and there are no results returned,
SponsorCount is left blank, but when there are, it of course returns
the number of results (i.e. "67").

How can I get it to return a zero if there are no results?

Thanks in advance!

magmike

 

[Nick 'The Database Guy']

I have an unbound field on a search form titled SponsorCount -

    =Count([SPONNAME])

When a search is completed, and there are no results returned,
SponsorCount is left blank, but when there are, it of course returns
the number of results (i.e. "67").

How can I get it to return a zero if there are no results?

Thanks in advance!

magmike

Hi magmike,

Try using the Nz function, it would look like this
'=Nz(Count([SPONNAME]))' or even '=Count(nz([SPONNAME])), hope this
helps.

Nick

 

[Klatuu]

=Nz(Count([SPONNAME]), 0)

The Nz will return vbNullString if no parameter is included.
--
Dave Hargis, Microsoft Access MVP

I have an unbound field on a search form titled SponsorCount -

=Count([SPONNAME])

When a search is completed, and there are no results returned,
SponsorCount is left blank, but when there are, it of course returns
the number of results (i.e. "67").

How can I get it to return a zero if there are no results?

Thanks in advance!

magmike

Hi magmike,

Try using the Nz function, it would look like this
'=Nz(Count([SPONNAME]))' or even '=Count(nz([SPONNAME])), hope this
helps.

Nick

 

[magmike]

=Nz(Count([SPONNAME]), 0)

The Nz will return vbNullString if no parameter is included.
--
Dave Hargis, Microsoft Access MVP

:

I have an unbound field on a search form titled SponsorCount -
    =Count([SPONNAME])
When a search is completed, and there are no results returned,
SponsorCount is left blank, but when there are, it of course returns
the number of results (i.e. "67").
How can I get it to return a zero if there are no results?
Thanks in advance!
magmike

Himagmike,

Try using the Nz function, it would look like this
'=Nz(Count([SPONNAME]))' or even '=Count(nz([SPONNAME])), hope this
helps.

Nick- Hide quoted text -

- Show quoted text -

None of these are working! Does it make any difference that the field
and the results are on a subform, but the unbound search field and
search command button are on the parent form?

It still shows the correct count when it is above zero, however.

 

[Klatuu]

Okay this is a bit different that what I first read.
When you do this search, what are you search on? The form's recordsset, the
subform's recordset, a table, a query? There appears to be more to it than I
am understanding. Can you describe in some detail what you are doing, please.
--
Dave Hargis, Microsoft Access MVP

=Nz(Count([SPONNAME]), 0)

The Nz will return vbNullString if no parameter is included.
--
Dave Hargis, Microsoft Access MVP

:

I have an unbound field on a search form titled SponsorCount -

=Count([SPONNAME])

When a search is completed, and there are no results returned,
SponsorCount is left blank, but when there are, it of course returns
the number of results (i.e. "67").

How can I get it to return a zero if there are no results?

Thanks in advance!

magmike

Himagmike,

Try using the Nz function, it would look like this
'=Nz(Count([SPONNAME]))' or even '=Count(nz([SPONNAME])), hope this
helps.

Nick- Hide quoted text -

- Show quoted text -

None of these are working! Does it make any difference that the field
and the results are on a subform, but the unbound search field and
search command button are on the parent form?

It still shows the correct count when it is above zero, however.

 

[magmike]

Okay this is a bit different that what I first read.
When you do this search, what are you search on?  The form's recordsset, the
subform's recordset, a table, a query?  There appears to be more to it than I
am understanding.  Can you describe in some detail what you are doing, please.
--
Dave Hargis, Microsoft Access MVP

=Nz(Count([SPONNAME]), 0)
The Nz will return vbNullString if no parameter is included.
--
Dave Hargis, Microsoft Access MVP
:
I have an unbound field on a search form titled SponsorCount -
    =Count([SPONNAME])
When a search is completed, and there are no results returned,
SponsorCount is left blank, but when there are, it of course returns
the number of results (i.e. "67").
How can I get it to return a zero if there are no results?
Thanks in advance!
magmike
Himagmike,
Try using the Nz function, it would look like this
'=Nz(Count([SPONNAME]))' or even '=Count(nz([SPONNAME])), hope this
helps.
Nick- Hide quoted text -
- Show quoted text -

None of these are working! Does it make any difference that the field
and the results are on a subform, but the unbound search field and
search command button are on the parent form?

It still shows the correct count when it is above zero, however.- Hide quoted text -

- Show quoted text -

The parent form does not have a record source. The parent form houses
three different subforms that each are driven by queries that access
different data sources (tables). As part of the search criteria, the
user selects the data source they are using and the proper subform is
made visible and used for the search.

Each subform uses a simple query that is used primarily to isolate the
necessary fields and for sorting.

 

[magmike]

Okay this is a bit different that what I first read.
When you do this search, what are you search on?  The form's recordsset, the
subform's recordset, a table, a query?  There appears to be more to it than I
am understanding.  Can you describe in some detail what you are doing, please.

=Nz(Count([SPONNAME]), 0)
The Nz will return vbNullString if no parameter is included.
--
Dave Hargis, Microsoft Access MVP
:
I have an unbound field on a search form titled SponsorCount -
    =Count([SPONNAME])
When a search is completed, and there are no results returned,
SponsorCount is left blank, but when there are, it of course returns
the number of results (i.e. "67").
How can I get it to return a zero if there are no results?
Thanks in advance!
magmike
Himagmike,
Try using the Nz function, it would look like this
'=Nz(Count([SPONNAME]))' or even '=Count(nz([SPONNAME])), hope this
helps.
Nick- Hide quoted text -
- Show quoted text -
None of these are working! Does it make any difference that the field
and the results are on a subform, but the unbound search field and
search command button are on the parent form?
It still shows the correct count when it is above zero, however.- Hide quoted text -

- Show quoted text -

The parent form does not have a record source. The parent form houses
three different subforms that each are driven by queries that access
different data sources (tables). As part of the search criteria, the
user selects the data source they are using and the proper subform is
made visible and used for the search.

Each subform uses a simple query that is used primarily to isolate the
necessary fields and for sorting.- Hide quoted text -

- Show quoted text -

It should also be noted, that the unbound field housing the count
code, is on each subform to count the results

 

[Klatuu]

Is this Count the number of records in the subform?
--
Dave Hargis, Microsoft Access MVP

Okay this is a bit different that what I first read.
When you do this search, what are you search on? The form's recordsset, the
subform's recordset, a table, a query? There appears to be more to it than I
am understanding. Can you describe in some detail what you are doing, please.

:
=Nz(Count([SPONNAME]), 0)

The Nz will return vbNullString if no parameter is included.

"Nick 'The Database Guy'" wrote:

I have an unbound field on a search form titled SponsorCount -

=Count([SPONNAME])

When a search is completed, and there are no results returned,
SponsorCount is left blank, but when there are, it of course returns
the number of results (i.e. "67").

How can I get it to return a zero if there are no results?

Thanks in advance!

magmike

Himagmike,

Try using the Nz function, it would look like this
'=Nz(Count([SPONNAME]))' or even '=Count(nz([SPONNAME])), hope this
helps.

Nick- Hide quoted text -

- Show quoted text -

None of these are working! Does it make any difference that the field
and the results are on a subform, but the unbound search field and
search command button are on the parent form?

It still shows the correct count when it is above zero, however.- Hide quoted text -

- Show quoted text -

The parent form does not have a record source. The parent form houses
three different subforms that each are driven by queries that access
different data sources (tables). As part of the search criteria, the
user selects the data source they are using and the proper subform is
made visible and used for the search.

Each subform uses a simple query that is used primarily to isolate the
necessary fields and for sorting.- Hide quoted text -

- Show quoted text -

It should also be noted, that the unbound field housing the count
code, is on each subform to count the results

 

[magmike]

Is this Count the number of records in the subform?
--
Dave Hargis, Microsoft Access MVP

Okay this is a bit different that what I first read.
When you do this search, what are you search on?  The form's recordsset, the
subform's recordset, a table, a query?  There appears to be more to it than I
am understanding.  Can you describe in some detail what you are doing, please.
--
Dave Hargis, Microsoft Access MVP
:
=Nz(Count([SPONNAME]), 0)
The Nz will return vbNullString if no parameter is included.
--
Dave Hargis, Microsoft Access MVP
:
I have an unbound field on a search form titled SponsorCount -
    =Count([SPONNAME])
When a search is completed, and there are no results returned,
SponsorCount is left blank, but when there are, it of course returns
the number of results (i.e. "67").
How can I get it to return a zero if there are no results?
Thanks in advance!
magmike
Himagmike,
Try using the Nz function, it would look like this
'=Nz(Count([SPONNAME]))' or even '=Count(nz([SPONNAME])),hope this
helps.
Nick- Hide quoted text -
- Show quoted text -
None of these are working! Does it make any difference that the field
and the results are on a subform, but the unbound search field and
search command button are on the parent form?
It still shows the correct count when it is above zero, however.-Hide quoted text -
- Show quoted text -
The parent form does not have a record source. The parent form houses
three different subforms that each are driven by queries that access
different data sources (tables). As part of the search criteria, the
user selects the data source they are using and the proper subform is
made visible and used for the search.
Each subform uses a simple query that is used primarily to isolate the
necessary fields and for sorting.- Hide quoted text -
- Show quoted text -

It should also be noted, that the unbound field housing the count
code, is on each subform to count the results- Hide quoted text -

- Show quoted text -

Yes! The field that counts is also on the subform. Each subform has
one.

 

[Klatuu]

I think this should work for you. The control that does the count should be
in the footer section of the subform. It will not be visible, so you will
want to put a control on the main form that uses its control source to show
the value of the control on the subform footer.


For example. You could name the control on the subform footer
txtCountSponcers. Its control source would be

=Count([SPONNAME])


Then on the main form you woul use:

= Nz(SubformControlName.Form.txtCountSponcers,0)
--
Dave Hargis, Microsoft Access MVP

Is this Count the number of records in the subform?
--
Dave Hargis, Microsoft Access MVP

On Aug 28, 3:24 pm, Klatuu <[email protected]> wrote:

Okay this is a bit different that what I first read.
When you do this search, what are you search on? The form's recordsset, the
subform's recordset, a table, a query? There appears to be more to it than I
am understanding. Can you describe in some detail what you are doing, please.

:
=Nz(Count([SPONNAME]), 0)

The Nz will return vbNullString if no parameter is included.

"Nick 'The Database Guy'" wrote:

I have an unbound field on a search form titled SponsorCount -

=Count([SPONNAME])

When a search is completed, and there are no results returned,
SponsorCount is left blank, but when there are, it of course returns
the number of results (i.e. "67").

How can I get it to return a zero if there are no results?

Thanks in advance!

magmike

Himagmike,

Try using the Nz function, it would look like this
'=Nz(Count([SPONNAME]))' or even '=Count(nz([SPONNAME])), hope this
helps.

Nick- Hide quoted text -

- Show quoted text -

None of these are working! Does it make any difference that the field
and the results are on a subform, but the unbound search field and
search command button are on the parent form?

It still shows the correct count when it is above zero, however.- Hide quoted text -

- Show quoted text -

The parent form does not have a record source. The parent form houses
three different subforms that each are driven by queries that access
different data sources (tables). As part of the search criteria, the
user selects the data source they are using and the proper subform is
made visible and used for the search.

Each subform uses a simple query that is used primarily to isolate the
necessary fields and for sorting.- Hide quoted text -

- Show quoted text -

It should also be noted, that the unbound field housing the count
code, is on each subform to count the results- Hide quoted text -

- Show quoted text -

Yes! The field that counts is also on the subform. Each subform has
one.

 

[magmike]

I think this should work for you.  The control that does the count should be
in the footer section of the subform.  It will not be visible, so you will
want to put a control on the main form that uses its control source to show
the value of the control on the subform footer.

For example.  You could name the control on the subform footer
txtCountSponcers.  Its control source would be

 =Count([SPONNAME])

Then on the main form you woul use:

= Nz(SubformControlName.Form.txtCountSponcers,0)
--
Dave Hargis, Microsoft Access MVP

Is this Count the number of records in the subform?
--
Dave Hargis, Microsoft Access MVP
:
Okay this is a bit different that what I first read.
When you do this search, what are you search on?  The form's recordsset, the
subform's recordset, a table, a query?  There appears to be more to it than I
am understanding.  Can you describe in some detail what you are doing, please.
--
Dave Hargis, Microsoft Access MVP
:
=Nz(Count([SPONNAME]), 0)
The Nz will return vbNullString if no parameter is included..
--
Dave Hargis, Microsoft Access MVP
:
I have an unbound field on a search form titled SponsorCount -
    =Count([SPONNAME])
When a search is completed, and there are no results returned,
SponsorCount is left blank, but when there are, it of course returns
the number of results (i.e. "67").
How can I get it to return a zero if there are no results?
Thanks in advance!
magmike
Himagmike,
Try using the Nz function, it would look like this
'=Nz(Count([SPONNAME]))' or even '=Count(nz([SPONNAME])), hope this
helps.
Nick- Hide quoted text -
- Show quoted text -
None of these are working! Does it make any difference that the field
and the results are on a subform, but the unbound search field and
search command button are on the parent form?
It still shows the correct count when it is above zero, however.- Hide quoted text -
- Show quoted text -
The parent form does not have a record source. The parent form houses
three different subforms that each are driven by queries that access
different data sources (tables). As part of the search criteria, the
user selects the data source they are using and the proper subform is
made visible and used for the search.
Each subform uses a simple query that is used primarily to isolate the
necessary fields and for sorting.- Hide quoted text -
- Show quoted text -
It should also be noted, that the unbound field housing the count
code, is on each subform to count the results- Hide quoted text -
- Show quoted text -

Yes! The field that counts is also on the subform. Each subform has
one.

Still not working. Not sure why. I tried all sorts of If statements
(such as If the count field is not null, then use it, else display "0"
or if the count field is > 0 and so forth) on and off the subform, and
all I get is #Error.

I think I give up (for now).