Expression Sum IIf

[ralphyehle]

I want to create a field named ErrorCases that sums the rows meeting the following conditions: The row has a ValidityReason = 2 and for the same row ExpValidity is either null or 3. I want a count of the rows meeting these condtions.

Here is the formula I have constructed that does not work.

ErrorCases: Sum(IIf(([ValidityReason]="2" And [ExpValidity] Is Null) Or ([ValidityReason]="2" And [ExpValidity]="3"),1,0))

Any suggestions?

 

[Bat17]

don't wrap the numbers in quotes, unless they really are stored as text

Peter

 

[Vassago]

And don't post the same question more than once.

Try this if the fields really are text:

Sum(Iif([ValidityReason] = '2' and ([ExpValidity] is Null Or [ExpValidity] = '3'),1,0))

or this if the fields really are numbers:

Sum(Iif([ValidityReason] = 2 and ([ExpValidity] is Null Or [ExpValidity] = 3),1,0))

or even more simply, do a count query:

SELECT Count([ValidityReason]) AS CountOfValidityReason
FROM YourTable
HAVING [ValidityReason]='2' And ([ExpValidity] Is Null Or [ExpValidity]='3')