Stopping zero printing in a report

  • Thread starter Thread starter Noel
  • Start date Start date
N

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

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

=Abs(Sum([SchoolName] > 0))
 
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
Click to expand...

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.<<


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

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

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