Text Box Control Source with formatting and filtering

[Mike Smith]

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!

 

[AlanS]

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.

 

[Abby N]

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).]