Counting a record in the same field (Access Report) 2

[mouseman]

I have a Report, and i would like to count a specific record. For example,
The report is grouped on name and I have another Field Name called OTYP, which means Order Type. I want to count a specific record, for example EX, from the field name OTYP in the Report Footer. I tried to enter an IIF statement, also a countif, but does not seem to work. My formula was this =IIF([OTYP]="ex",Count,0. Please help. Thanks

 

[sko]

=DCount("[OTYP]","YourDomain","[OTYP]='ex'&quot

Where Your Domain = the name of the qry or table that is the record source for your report.

HTH

 

[mouseman]

Thanks, it works like a charm. Thanks Again

 

[Guest_imported]

I would like to generate a unique number based on the year/month/cust. For instance if I would enter the fourth customer for the month on June that customer ID would be
020604. 02: Year,06: Month, 04: fourth customer entered that month. The customer number need to be set to 1 at the beginning of each month.

 

[AMW1106]

I am attempting to create a similar report but am trying to calculate a count of a specific dollar amount. My report includes a column of names, a column of account numbers and a column of fees. I need to calculate the number of times a specific fee amount occurred. I used the above formula and am getting #Error. I have listed the formula used below. Any suggestions???

=DCount("[Fee Amt]","PLL FEE Qry","[Fee_ Amt]='$5.00'&quot

 

[aogail]

I'm having the same problem as AMW1106. I have a field I would like to count only when it is greater than zero. Using the following generates #Error instead of a count:

=DCount("[Lbs_Sampled]","ONP Quality Statistics for Supplier (BJ - 7/24/03)","[Lbs_Sampled]>0")

[Lbs_Sampled] is a numeric field and "ONP Quality Statistics for Supplier (BJ - 7/24/03" is the query that the report pulls data from. Can anyone point out my error?

Thanks!

 

[dhookom]

DON'T use DCount() to solve any of these problems. If you open the report with a "Where" clause, the report will be filter but the DCount() will still be looking at a larger data set. Besides, DCount() and DSum() open new recordset which slows down your report.

If you want to count the occurance of a specific value or condition in a report, you should use a control source like:
Count the number where [Gender] = "M":
=Abs( Sum([Gender] = "M&quot )
Sum the salaries where [Gender] = "M":
=Abs( Sum(([Gender] = "M&quot * [Salary]) )


Consider:
=Abs(Sum([OTYP]="ex&quot)
=Abs(Sum([Lbs_Sampled]>0))
=Abs(Sum([Fee_ Amt]=5))
These are all faster and more accurate than using DCount.



Duane
MS Access MVP

 

[aogail]

Thank you very much, Duane! Your help is greatly appreciated.

Could you please explain why this method works? I don't understand why Abs(Sum(expr)) returns the number of records matching expr.

Thanks again,
Ben

 

[dhookom]

The expr will evaluate to either True/-1 or False/0. So it is summing the expr (either 0 or -1). This will result in minus the number of times the expr is True. The Abs() function converts the negative values to positive.

If you want to sum another field, you then multiply the -1 or 0 times the other field and then sum it and then Abs() it.

Duane
MS Access MVP

 

[aogail]

Great! Thank you for the explanation.

Ben