How do I count records using an iif statement?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Kelvin Beaton - 30 Mar 2007 17:34 GMT

I want to get a count of the number of records where the field [TaskName]
equals "Face to Face" and the field [Message] is blank
I've tried this in a text box on the report:
=IIf([Message] Is Null,(Sum(Abs([TaskName]="Face to Face"))),"no")

I know this part works:
(Sum(Abs([TaskName]="Face to Face")

This statement in a query:
=IIf([Message] Is Null,(Sum(Abs([TaskName]="Face to Face"))),"no")
Gives me this error message:
You tried to execute a query that does not include the specified expression
'TaskID' as part of an aggregate function.

This is what the query looks like:
SELECT tbl_TasksDue.*, IIf([Message] Is Null,(Sum(Abs([TaskName]="Face to
Face"))),"") AS test
FROM tbl_TasksDue
WHERE (((tbl_TasksDue.CaseStatus)="1" Or (tbl_TasksDue.CaseStatus)="2") AND
((tbl_TasksDue.CloseDate) Is Null));

"TaskID" is the primary key of the table "tbl_TasksDue"

Can someone point me in the right direction?

Thanks

Kelvin

Reply to this Message

Ken Sheridan - 30 Mar 2007 18:16 GMT

Kelvin:

Firstly don't use the fact that Boolean values are implemented in Access as
0 or -1. Its not good practice. The head of a software company of my
acquaintance once described it as being "unduly chummy with the
implementation". Instead Sum the return value of an expression which
returns 1 or 0.

You then need to use a Boolean AND operation to determine if both conditions
are TRUE, the value of TaskName and Message being NULL, so the complete
expression would be:

SUM(IIF(TaskName = "Face to face" AND Message IS NULL,1,0))

To use this as a column in the query you'd need to use a subquery and
restrict in on the same columns as the outer query:

SELECT tbl_TasksDue.*,
(SELECT SUM(IIF(TaskName = "Face to face" AND Message IS NULL,1,0))
FROM tbl_TasksDue
WHERE CaseStatus IN("1","2")
AND CloseDate IS NULL)
AS TaskCount
FROM tbl_TasksDue
WHERE CaseStatus IN("1","2")
AND CloseDate IS NULL;

In a computed control in a report footer or group footer (for a subtotal)
you'd use a similar expression:

=Sum(IIf([TaskName] = "Face to face" And IsNull([Message]),1,0))

Ken Sheridan
Stafford, England

> I want to get a count of the number of records where the field [TaskName]
> equals "Face to Face" and the field [Message] is blank
[quoted text clipped - 24 lines]
>
> Kelvin

Reply to this Message