Query Report group count

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

HI

There is a report that shows all issues opened by Opened Date. The issues are either have open or closed status
Open Date can be a range of dates
The report is based on a query. It groups records by Open Date. It count number of record for each Open Date group. Then it counts all records for the report at the report footer

I am trying to add another count of records within a group based on status of the issue (0 or -1).

Here is my coding for the Control Source for the report footer

=DCount("[IsClosed]","[rpt-Call-Sum-Calls Opened by Date]","[IsClosed] = 0"

Naturally it counts ALL records with this condition in the query

How can I count records with the same value for [IsClosed] =0 for a particular group within the report (for each Open Day)

Thank you
Fay
 
Faye said:
HI,

There is a report that shows all issues opened by Opened Date. The issues are either have open or closed status.
Open Date can be a range of dates.
The report is based on a query. It groups records by Open Date. It count number of record for each Open Date group. Then it counts all records for the report at the report footer.

I am trying to add another count of records within a group based on status of the issue (0 or -1).

Here is my coding for the Control Source for the report footer:

=DCount("[IsClosed]","[rpt-Call-Sum-Calls Opened by Date]","[IsClosed] = 0")

Naturally it counts ALL records with this condition in the query.


How can I count records with the same value for [IsClosed] =0 for a particular group within the report (for each Open Day)?
Click to expand...


You can use an expressions like any of these:

=Count(IIf([IsClosed] = 0, 1, Null))
=Sum(IIf([IsClosed] = 0, 1, 0)
=Abs(Sum([IsClosed] = 0))

to count the records with closed = 0 in the group footer
and/or report footer.
 
Marshall

Thank you so much! It did work

Now I need to ask you this. Can Dcount has more than one criteria? If yes, what is the syntax

Thanks
Faye
 
Faye said:
Marshall,

Thank you so much! It did work.
Click to expand...

You're welcome.
Now I need to ask you this. Can Dcount has more than one criteria? If yes, what is the syntax?
Click to expand...

Sure, you can combine multiple criteria using And and Or,
depending on what you want to do.

=DCount("[IsClosed]","table]",
"[IsClosed] = 0 And [CloseDate] < #2/9/04#")

=DCount("*","table]",
"PayType = ""Cash"" Or PayType = ""Check""")
 
Marshall

Please disregard my previous cry for help. Everything is working

Thank you very much

Fay
 
Back
Top