Expression to count certain rows

[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?

 

[Remou]

Are ValidityReason and ExpValidity numeric or text fields? You do not need quotes ("2") for numeric fields.

 

[PHV]

that does not work
Any error message ?
What is the SQL code of your actual query ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[ralphyehle]

This is a query that groups on a field that names counties, so the count is how many ErrorCases are there in each county. The ValidityReason and the ExpValidity fields are both text fields even though populated by numbers, it is possible they could contain a letter.

 

[Remou]

I have tried a mock-up, and it seems to work for me, so I can only quote PHV, "What is the SQL code of your actual query ?"

 

[ralphyehle]

thanks for the help! all I need to do was to replace the Is Null qualifier with "". Field is text so Is Null did not work.