DCount based on parameter query results

Question

I have a query that returns records of actions (Action_Query_1) from a table. This query includes fields like Action_ID, Status and Department. I have set up the Department field as a parameter query.

I have a second query (Action_Query_2) that is based on the first query and that I am using to get only the Open actions for the specified Department.

I have created a report from Action_Query_2 to display all of the open actions for a specified department.

i would like to include a field on the report that counts the total number of actions for the specified department but am having trouble getting it to work with the parameter query.

I tried using DCount taking data from Action_Query_1 which contains all actions, but it doesn't look like it's picking up on the parameter input for the Department;

=DCount("[Action_ID]","[Action_Query_1]")

Any help would be appreciated!

Thanks!

Answer 1

You cannot call the DCount function against your current query.  You can either:

1.  Use the function I posted, which passes the parameter to the querydef object.

2.  Change the parameter in your query to a reference to a control on an open form and call the DCount function without a criterion:

=DCount("*","[Action_Query_1]")

3.  Create a copy of the query under a new name and remove the parameter.  Then call the DCount function with a criterion:

=DCount("*","[Action_Query_1_Without_Param]","[Department]=""" & [Department] & """")

Answer 2

Is there any way to write an expression for the input to the department parameter query as the criteria of the DCount expression?

Is there any thing I can put in place of parameterqueryresult in the following expression?

=DCount("[Action_ID]","[Action_Query_1]","[Department]=parameterqueryresult")

I want to try and keep this as simple as possible...if it's even possible!

Answer 3

You could write your own function:

Public Function ActionCount(strDepartment As String) As Integer

    Dim rst As DAO.Recordset

    Dim qdf As DAO.QueryDef

    Set qdf = CurrentDb.QueryDefs("Action_Query_1")

    qdf.Parameters(0) = strDepartment

    Set rst = qdf.OpenRecordset

    With rst

        .MoveLast

        ActionCount = .RecordCount

        .Close

    End With

    Set rst = Nothing

End Function

In the report add a text box with a ControlSource property of:

=ActionCount([Department])

Answer 4

Here is a different approach.

Based upon how many Statuses you have just add into the first query --

SELECT Action_ID, Department, Sum(IIF([Status] = "Open", 1, 0)) AS CountOfOpen, Sum(IIF([Status] = "Close", 1, 0)) AS CountOfClose

FROM tblTable;

SELECT [Action_Query_1].Department, tblTable.Xxxxx, [CountOfOpen] + [CountOfClose] AS TotalActions

FROM  [Action_Query_1] LEFT JOIN tblTable ON [Action_Query_1].Department = tblTable.Department;

 tblTable.Xxxxx would be all the fields you need from tblTable.