Query Report group count

[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

 

[Marshall Barton]

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)?

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.

 

[Guest]

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

 

[Marshall Barton]

Marshall,

Thank you so much! It did work.

You're welcome.

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

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""")

 

[Guest]

Marshall

Please disregard my previous cry for help. Everything is working

Thank you very much

Fay