sum iif and blank question

[gmazza76]

Good afternoon

I am trying to create a field in my query that leaves a 1 if a Question = "Yes" and another Field isblank if thats possible.

I have tried the following, but the formula seems to add up all the empty column references and misses the "Yes" clause. I think I am missing something simple but I cant seem to see it
I am trying to identify is Q7 says "Yes" and the Completed is blank. If its blank I need a 0 output

Remediation Completed : Sum(IIf([Table]![Q7]="Yes" And [Table]![Completed] Is Null,0,1))
Remediation Completed : Sum(IIf([Table]![Q7]="Yes" And isNull([Table]![Completed],0,1))

thanks in advance
Gavin

 

[gmazza76]

I managed to calculated this problem as follows
Calculating the volume that was marked and then used "IsNull" to find the amount that had been completed and minused them from each other

Remediation Completed: Sum(IIf([Q7]="Yes",1,0))-Sum(IIf([Q7]="Yes" And IsNull([RemediationCompletedBy]),1,0))