Report problems

[Mark]

Hi,

I am trying to count the number of times "Yes" or "No"
occurs on a report. In the detail section of a report I
have a text box named "TimelinessYesNo". I would like to
count the number of occurrences of "Yes" and "No"
results.

I have placed a text box in both the header and footer to
make this work, but I am having no luck. Also, I have
tried a couple of control source coding, but still am
having no luck. Here are some of the coding I have tried:

For "Yes" results:

=Sum(IIF([TimelinessYesNo],1,0))
or
=Count(IIF([TimelinessYesNo],1,0))
or
=Count(IIF([TimelinessYesNo]=1,1,0))
where "[TimelinessYesNo]=1" equals "Yes"

Any suggestions? Thanks a bunch!!!!

 

[Bruce Pick]

You were close. The numeric value for Yes in Access is -1. I think the
idea was to have the Yes values show first when sorting normally
(ascending). It's just not the expected value of 1.

These expressions work for the values of text boxes.
They must be used in Report Header and/or Footer -
not the Page Header or Footer. Or you could use them in Group Headers
if you have some of those

=Count([TimelinessYesNo]) will give the # of records in the dataset.
=Abs(Sum([TimelinessYesNo])) will give the # of "Yes" records.
=Count([TimelinessYesNo])-Abs(Sum([TimelinessYesNo])) gives the
difference, or # of No values.

Abs() is Absolute Value, which essentially converts any negative # to
positive.

Bruce Pick

 

[Bruce Pick]

Oh, yes - if using a formula in a text box, make sure that the name of
the text box is NOT the name of a field in the report's source.
(as when converting a box originally dragged into the report from the
field list). This will cause an error which you can fix by changing the
text box name (Properties box, "Other" tab).

Bruce Pick

 

[Mark]

Awesome Bruce!!!! Thanks for the quick help!!!

-----Original Message-----
You were close. The numeric value for Yes in Access is - 1. I think the
idea was to have the Yes values show first when sorting normally
(ascending). It's just not the expected value of 1.

These expressions work for the values of text boxes.
They must be used in Report Header and/or Footer -
not the Page Header or Footer. Or you could use them in Group Headers
if you have some of those

=Count([TimelinessYesNo]) will give the # of records in the dataset.
=Abs(Sum([TimelinessYesNo])) will give the # of "Yes" records.
=Count([TimelinessYesNo])-Abs(Sum([TimelinessYesNo])) gives the
difference, or # of No values.

Abs() is Absolute Value, which essentially converts any negative # to
positive.

Bruce Pick

Hi,

I am trying to count the number of times "Yes" or "No"
occurs on a report. In the detail section of a report I
have a text box named "TimelinessYesNo". I would like to
count the number of occurrences of "Yes" and "No"
results.

I have placed a text box in both the header and footer to
make this work, but I am having no luck. Also, I have
tried a couple of control source coding, but still am
having no luck. Here are some of the coding I have tried:

For "Yes" results:

=Sum(IIF([TimelinessYesNo],1,0))
or
=Count(IIF([TimelinessYesNo],1,0))
or
=Count(IIF([TimelinessYesNo]=1,1,0))
where "[TimelinessYesNo]=1" equals "Yes"

Any suggestions? Thanks a bunch!!!!

.

 

[Bruce Pick]

You're welcome!

Bruce