Stopping zero printing in a report

[Noel]

Hi, I have a query that uses Count to produce a total for
a certain field (SchoolName) and a report that prints
this value. How can I stop zero (0) printing? I only want
to see a count if its 1 or above. Can anyone think of
some On Print event code I could use, or is there a
simpler way? Thanks, Noel

 

[Rod]

If you are doing the counting in your query you could use
a having clause specifying count(SchooName)>0. This would
eliminate the record from the set.

 

[Eric]

Set the textbox ControlSource to:

=IIf([CountFromQuery]>0,[CountFromQuery],"")

HTH....Eric

 

[Bruce M. Thompson]

Hi, I have a query that uses Count to produce a total for

a certain field (SchoolName) and a report that prints
this value. How can I stop zero (0) printing? I only want
to see a count if its 1 or above. Can anyone think of
some On Print event code I could use, or is there a
simpler way? Thanks, Noel

If you are just looking for the count, then try:

=Abs(Sum([SchoolName] > 0))

 

[Noel]

Thank you all for the replys. Ill give them a try.
Cgheers, Noel

-----Original Message-----

Hi, I have a query that uses Count to produce a total for
a certain field (SchoolName) and a report that prints
this value. How can I stop zero (0) printing? I only want
to see a count if its 1 or above. Can anyone think of
some On Print event code I could use, or is there a
simpler way? Thanks, Noel

If you are just looking for the count, then try:

=Abs(Sum([SchoolName] > 0))

--
Bruce M. Thompson, Microsoft Access MVP
(e-mail address removed) (See the Access FAQ at http://www.mvps.org/access)within the newsgroups so that all might benefit.<<


.

 

[Noel]

Eric, is your code correct? I just tried it with my own
field name of CountOfSchoolName in place of CountFrom
Query and it comes up as #error. Is this what you meant?
Thanks, Noel

-----Original Message-----
Set the textbox ControlSource to:

=IIf([CountFromQuery]>0,[CountFromQuery],"")

HTH....Eric

-----Original Message-----
Hi, I have a query that uses Count to produce a total for
a certain field (SchoolName) and a report that prints
this value. How can I stop zero (0) printing? I only want
to see a count if its 1 or above. Can anyone think of
some On Print event code I could use, or is there a
simpler way? Thanks, Noel
.

.

 

[Eric]

I ran a quicky test and found two possible problems.
This stuff can be cranky at times for the most obscure
reasons.

Check the Name property of the textbox. If you used the
wizard or did a drag-drop from the query to establish the
textbox, the Name property will be the same as the
Control Source property. This confuses the IIf statement
as it seems not to know whether to act on the query field
name or on the textbox name. Change the Name property of
the textbox to SchoolCount or some such. Most likely,
Access will thoughtfully change the Control Source field
names to match, so you'll need to edit and change them
back to CountOfSchoolName. This could be all you need to
do.

If you still getting #Error, the problem is that
CountOfSchoolName isn't in fact returning 0 when there
are no names to count. That was the case in my quick
test. I changed the textbox statement to be:

=IIf(IsNumeric([CountOfSchoolName])=True,
[CountOfSchoolName],"")

Both of the above changes gave me the desired result.
HTH....Eric

-----Original Message-----
Eric, is your code correct? I just tried it with my own
field name of CountOfSchoolName in place of CountFrom
Query and it comes up as #error. Is this what you meant?
Thanks, Noel

-----Original Message-----
Set the textbox ControlSource to:

=IIf([CountFromQuery]>0,[CountFromQuery],"")

HTH....Eric

-----Original Message-----
Hi, I have a query that uses Count to produce a total for
a certain field (SchoolName) and a report that prints
this value. How can I stop zero (0) printing? I only want
to see a count if its 1 or above. Can anyone think of
some On Print event code I could use, or is there a
simpler way? Thanks, Noel
.

.

.

 

[Noel]

Thanks Eric. I wont be able to try this at work until
Monday (possibly Tuesday) of next week. Will report back
when I can. Thanks again, Noel

-----Original Message-----
I ran a quicky test and found two possible problems.
This stuff can be cranky at times for the most obscure
reasons.

Check the Name property of the textbox. If you used the
wizard or did a drag-drop from the query to establish the
textbox, the Name property will be the same as the
Control Source property. This confuses the IIf statement
as it seems not to know whether to act on the query field
name or on the textbox name. Change the Name property of
the textbox to SchoolCount or some such. Most likely,
Access will thoughtfully change the Control Source field
names to match, so you'll need to edit and change them
back to CountOfSchoolName. This could be all you need to
do.

If you still getting #Error, the problem is that
CountOfSchoolName isn't in fact returning 0 when there
are no names to count. That was the case in my quick
test. I changed the textbox statement to be:

=IIf(IsNumeric([CountOfSchoolName])=True,
[CountOfSchoolName],"")

Both of the above changes gave me the desired result.
HTH....Eric

-----Original Message-----
Eric, is your code correct? I just tried it with my own
field name of CountOfSchoolName in place of CountFrom
Query and it comes up as #error. Is this what you meant?
Thanks, Noel

-----Original Message-----
Set the textbox ControlSource to:

=IIf([CountFromQuery]>0,[CountFromQuery],"")

HTH....Eric


-----Original Message-----
Hi, I have a query that uses Count to produce a total
for
a certain field (SchoolName) and a report that prints
this value. How can I stop zero (0) printing? I only
want
to see a count if its 1 or above. Can anyone think of
some On Print event code I could use, or is there a
simpler way? Thanks, Noel
.

.

.

.

 

[Noel]

Hi again Eric. Just to let you know I fixed this problem
using =IIf([CountOfSchoolName]>0,[CountOfSchoolName],"")
with then textbox name not CountOfSchoolName. This has
also solved a problem Ive had in the past which I now
know to have been caused by using the same text box name
as used in the control source code. So thats two problems
youve solved for the price of one!

Thank you for all your help, Noel

-----Original Message-----
I ran a quicky test and found two possible problems.
This stuff can be cranky at times for the most obscure
reasons.

Check the Name property of the textbox. If you used the
wizard or did a drag-drop from the query to establish the
textbox, the Name property will be the same as the
Control Source property. This confuses the IIf statement
as it seems not to know whether to act on the query field
name or on the textbox name. Change the Name property of
the textbox to SchoolCount or some such. Most likely,
Access will thoughtfully change the Control Source field
names to match, so you'll need to edit and change them
back to CountOfSchoolName. This could be all you need to
do.

If you still getting #Error, the problem is that
CountOfSchoolName isn't in fact returning 0 when there
are no names to count. That was the case in my quick
test. I changed the textbox statement to be:

=IIf(IsNumeric([CountOfSchoolName])=True,
[CountOfSchoolName],"")

Both of the above changes gave me the desired result.
HTH....Eric

-----Original Message-----
Eric, is your code correct? I just tried it with my own
field name of CountOfSchoolName in place of CountFrom
Query and it comes up as #error. Is this what you meant?
Thanks, Noel

-----Original Message-----
Set the textbox ControlSource to:

=IIf([CountFromQuery]>0,[CountFromQuery],"")

HTH....Eric


-----Original Message-----
Hi, I have a query that uses Count to produce a total
for
a certain field (SchoolName) and a report that prints
this value. How can I stop zero (0) printing? I only
want
to see a count if its 1 or above. Can anyone think of
some On Print event code I could use, or is there a
simpler way? Thanks, Noel
.

.

.

.