Calculated field in a report pulling data from two separate columns in a query

[lkyinc]

I have a database of all blood drives promoted by our organization. To look at a particular drive I query the date of that drive, then create a report based upon that query. For this post I will be dealing with two fields in the query: [Tag], and [Successful Donation], both are short text format. [Tag] indicates a regular blood donation with a "B", or a power red donation with a "P." [Successful Donation] indicates the result of attempt: Yes = blood was drawn, CG = could not give, C = Canceled, and NS = No show. I need an expression in a calculated field in the report that yields the count of the "Power Red donors" that were successful in their donation. In other words count the "P's" in the [Tag] column IF there is a corresponding "Yes" in [Successful Donation] column. Thank you for your help.

[June7]

=Count(IIf([Successful Donation]="Yes" AND [Tag]="P", "*", Null))

This will have to be in report or group Header/Footer section.

[ssanfu]

Be aware that "Tag" is a reserved word in Access (Form property; Form Section property; Report property; Report Section property).
Also, you should not use spaces in object names:
[Successful Donation] -> better would be [Successful_Donation]-> best might be [SuccessfulDonation]

[lkyinc]

=Count(IIf([Successful Donation]="Yes" AND [Tag]="P", "*", Null))

This will have to be in report or group Header/Footer section.

THR 7:30PM CST - Copied expression and it ran, which is an improvement, but it returned 0 for an answer. The correct answer is 5. 7 donors presented but only 5 were successful donors. Thanks for the attempt.

[ssanfu]

I just created a table, added fields "Tag" and "Successful Donation", added data.
I created a form, added two text box controls bound to the two fields, set to Continuous Forms View.
Added an unbound text box in the footer, set the control source to

=Count(IIf([Successful Donation]="Yes" And [Tag]="P","*",Null))

Pressed F5 (refresh) and 3 was displayed. Added two more records with "P" and "Yes", pressed F5 (refresh) and 5 was displayed.


Maybe post your dB? (change any sensitive data).

[lkyinc]

Thanks for your help. To post the DB do I just copy the table and paste here?

[Micron]

Follow instructions from link at top of forum page: "How to attach files"

[lkyinc]

Thanks Steve, Took the spaces out of the field names, but do not get the correct answer, even using your copied expression. When I run it, the result is 0. Kent

[ssanfu]

Kent,
So we know the formula works, now it is a matter of seeing your db for analysis.

see the signature line in Post #2 -June7's post