Field Counting in Queries and Reports

[Nick]

Hi,
I need to count the number of times a field called "Returns" has the
values "R" and "NI".
Unfortunately if I put this into the query it only brings those values
out with R or NI and this is not what I need.
I need a report to show the total number of times the value "R" and
"NI" has been selected.

In the past I have used commands like DateDiff to work things out e.g.:

13: IIf((DateDiff("w",[Date_Valid],[SentDate]))>13,1,0)

and then used a SUM command in the report e.g.:
=Sum([>13])
this has worked well.

but I cannot figure out how to make it count 2 character values and
display the result in the report.

can anyone help?

regards

Nick

 

[Brendan Reynolds]

If I understand the question correctly, I think the following may get you
the result you're looking for ...

=DCount("*", "YourTableNameHere", "Returns = 'R' OR Returns = 'NI'")

See 'DCount function' in the help file for more information.

--
Brendan Reynolds
Access MVP

Hi,
I need to count the number of times a field called "Returns" has the
values "R" and "NI".
Unfortunately if I put this into the query it only brings those values
out with R or NI and this is not what I need.
I need a report to show the total number of times the value "R" and
"NI" has been selected.

In the past I have used commands like DateDiff to work things out e.g.:

13: IIf((DateDiff("w",[Date_Valid],[SentDate]))>13,1,0)

and then used a SUM command in the report e.g.:
=Sum([>13])
this has worked well.

but I cannot figure out how to make it count 2 character values and
display the result in the report.

can anyone help?

regards

Nick

 

[Duane Hookom]

If you want to limit the count to only the records returned in your report,
add a text box with a control source of:

=Sum(Abs([Returns] In ("R","NI")))


--
Duane Hookom
MS Access MVP

Hi,
I need to count the number of times a field called "Returns" has the
values "R" and "NI".
Unfortunately if I put this into the query it only brings those values
out with R or NI and this is not what I need.
I need a report to show the total number of times the value "R" and
"NI" has been selected.

In the past I have used commands like DateDiff to work things out e.g.:

13: IIf((DateDiff("w",[Date_Valid],[SentDate]))>13,1,0)

and then used a SUM command in the report e.g.:
=Sum([>13])
this has worked well.

but I cannot figure out how to make it count 2 character values and
display the result in the report.

can anyone help?

regards

Nick