|  | Return a Zero with Count Function | | |
| | | magmike |  |
|  Posted: Thu Aug 28, 2008 2:29 pm Post subject: Return a Zero with Count Function |  |
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 | |
| | Posted: Thu Aug 28, 2008 3:01 pm Post subject: Re: Return a Zero with Count Function | |
On 28 Aug, 15:29, magmike <magmi...@yahoo.com> wrote:
| Quote: | 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 | |
| | Posted: Thu Aug 28, 2008 3:24 pm Post subject: Re: Return a Zero with Count Function | |
=Nz(Count([SPONNAME]), 0)
The Nz will return vbNullString if no parameter is included.
--
Dave Hargis, Microsoft Access MVP
"Nick 'The Database Guy'" wrote:
| Quote: | On 28 Aug, 15:29, magmike <magmi...@yahoo.com> 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
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 | |
| | Posted: Thu Aug 28, 2008 6:24 pm Post subject: Re: Return a Zero with Count Function | |
| |  | |
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
"magmike" wrote:
| Quote: | On Aug 28, 12:24 pm, Klatuu <Kla...@discussions.microsoft.com> wrote:
=Nz(Count([SPONNAME]), 0)
The Nz will return vbNullString if no parameter is included.
--
Dave Hargis, Microsoft Access MVP
"Nick 'The Database Guy'" wrote:
On 28 Aug, 15:29,magmike<magmi...@yahoo.com> 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.
|
|
| |
| | | Klatuu | |
| | Posted: Thu Aug 28, 2008 8:03 pm Post subject: Re: Return a Zero with Count Function | |
| | | |
Is this Count the number of records in the subform?
--
Dave Hargis, Microsoft Access MVP
"magmike" wrote:
| Quote: | On Aug 28, 4:38 pm, magmike <magmi...@yahoo.com> wrote:
On Aug 28, 3:24 pm, Klatuu <Kla...@discussions.microsoft.com> 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.
--
Dave Hargis, Microsoft Access MVP
"magmike" wrote:
On Aug 28, 12:24 pm, Klatuu <Kla...@discussions.microsoft.com> wrote:
=Nz(Count([SPONNAME]), 0)
The Nz will return vbNullString if no parameter is included.
--
Dave Hargis, Microsoft Access MVP
"Nick 'The Database Guy'" wrote:
On 28 Aug, 15:29,magmike<magmi...@yahoo.com> 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 | |
| | Posted: Thu Aug 28, 2008 8:11 pm Post subject: Re: Return a Zero with Count Function | |
| | | |
On Aug 28, 12:24 pm, Klatuu <Kla...@discussions.microsoft.com> wrote:
| Quote: | =Nz(Count([SPONNAME]), 0)
The Nz will return vbNullString if no parameter is included.
--
Dave Hargis, Microsoft Access MVP
"Nick 'The Database Guy'" wrote:
On 28 Aug, 15:29,magmike<magmi...@yahoo.com> 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. |
| |
| | | magmike | |
| | Posted: Thu Aug 28, 2008 9:38 pm Post subject: Re: Return a Zero with Count Function | |
| | | |
On Aug 28, 3:24 pm, Klatuu <Kla...@discussions.microsoft.com> wrote:
| Quote: | 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
"magmike" wrote:
On Aug 28, 12:24 pm, Klatuu <Kla...@discussions.microsoft.com> wrote:
=Nz(Count([SPONNAME]), 0)
The Nz will return vbNullString if no parameter is included.
--
Dave Hargis, Microsoft Access MVP
"Nick 'The Database Guy'" wrote:
On 28 Aug, 15:29,magmike<magmi...@yahoo.com> 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. |
| |
| | | magmike | |
| | Posted: Thu Aug 28, 2008 9:40 pm Post subject: Re: Return a Zero with Count Function | |
| | | |
On Aug 28, 4:38 pm, magmike <magmi...@yahoo.com> wrote:
| Quote: | On Aug 28, 3:24 pm, Klatuu <Kla...@discussions.microsoft.com> 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.
--
Dave Hargis, Microsoft Access MVP
"magmike" wrote:
On Aug 28, 12:24 pm, Klatuu <Kla...@discussions.microsoft.com> wrote:
=Nz(Count([SPONNAME]), 0)
The Nz will return vbNullString if no parameter is included.
--
Dave Hargis, Microsoft Access MVP
"Nick 'The Database Guy'" wrote:
On 28 Aug, 15:29,magmike<magmi...@yahoo.com> 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 | |
| | Posted: Mon Sep 01, 2008 3:26 am Post subject: Re: Return a Zero with Count Function | |
| | | |
On Aug 28, 5:03 pm, Klatuu <Kla...@discussions.microsoft.com> wrote:
| Quote: | Is this Count the number of records in the subform?
--
Dave Hargis, Microsoft Access MVP
"magmike" wrote:
On Aug 28, 4:38 pm,magmike<magmi...@yahoo.com> wrote:
On Aug 28, 3:24 pm, Klatuu <Kla...@discussions.microsoft.com> 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.
--
Dave Hargis, Microsoft Access MVP
"magmike" wrote:
On Aug 28, 12:24 pm, Klatuu <Kla...@discussions.microsoft.com> wrote:
=Nz(Count([SPONNAME]), 0)
The Nz will return vbNullString if no parameter is included.
--
Dave Hargis, Microsoft Access MVP
"Nick 'The Database Guy'" wrote:
On 28 Aug, 15:29,magmike<magmi...@yahoo.com> 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. |
| |
| | | Klatuu | |
| | Posted: Tue Sep 02, 2008 12:49 pm Post subject: Re: Return a Zero with Count Function | |
| | | |
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
"magmike" wrote:
| Quote: | On Aug 28, 5:03 pm, Klatuu <Kla...@discussions.microsoft.com> wrote:
Is this Count the number of records in the subform?
--
Dave Hargis, Microsoft Access MVP
"magmike" wrote:
On Aug 28, 4:38 pm,magmike<magmi...@yahoo.com> wrote:
On Aug 28, 3:24 pm, Klatuu <Kla...@discussions.microsoft.com> 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.
--
Dave Hargis, Microsoft Access MVP
"magmike" wrote:
On Aug 28, 12:24 pm, Klatuu <Kla...@discussions.microsoft.com> wrote:
=Nz(Count([SPONNAME]), 0)
The Nz will return vbNullString if no parameter is included.
--
Dave Hargis, Microsoft Access MVP
"Nick 'The Database Guy'" wrote:
On 28 Aug, 15:29,magmike<magmi...@yahoo.com> 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.
|
|
| |
| Page 1 of 2 .:. Goto page 1, 2 Next | |
|
|
