I'm trying to use the form footer to show totals of cases per agent and their value. The lines below work:

=[Agent] & " is responsible for " & Count([ID]) & " cases totalling " followed by =Sum([Amount]) and generates a line such as:

David Hall is responsible for 47 cases totalling $375,250

The sum is in a different text box that is butted up next to the previous one.

The above reports out ALL cases of any status and I want to limit it to those which are Active. I've used:

=[Agent] & " is responsible for " & Count([ID]) & " cases totalling " And ([Status]="Active") followed by =Sum([Amount]) And ([Status]="Active")

and a couple variations and have failed miserably. Any ideas???

Thanks!

Your problem may be due to your using the Count function (which counts all records in the underlying table or query) rather than the DCount function (which performs a similar operation, but also provides the option to limit the count to records meeting a specific criteria). My guess is that your underlying query returns all records, and so any reference to Count([whatever]) will count all records.

Looks like it’s a big day for Domain Aggregate functions. (Most of my suggestions today have used them. Which is noteworthy only because of how much I dislike them.) Anyway, give this a try.

=[Agent] & " is responsible for " & DCount("[ID]", "YourSourceTable", "([Status]= ‘Active’ and [Agent] = ‘" & [Agent] & "’" ) & " cases totaling " & Format(DSum("[Amount]", "YourSourceTable", "([Status]= ‘Active’ and [Agent] = ‘" & [Agent] & "’" ), "Currency")

This works in a single control but formats the total amount as currency. Which I assume is why you originally did it with two fields. Let me know if this causes any issues or you have any problems with this statement. Hope it helps.

~Abby

P.S.
Cheers to Alan for siting the problem and the solution. Sorry for overlapping his information. It wasn't there when I started my response.

[This message has been edited by Abby N (edited 08-21-2001).]