Well, been looking at this one for a bit and don't quite get it.
First, don't think you can use a TOP predicate unless you can sort the data ascending or descending, which it doesn't appear that you can here.
Second, non-grouped logical operators often don't work as expected. Let's remove some of the parentheses to make it more obvious and add others to make a point, then consider which of these you really mean:
([effective date]<=[pp end] And [Action]="HIR") or ([action]="REH",1,0)
([effective date]<=[pp end]) And ([Action]="HIR" or [action]="REH",1,0)
Your expression can evaluate to
IIF(True OR False) when the first comparison is True, but also
IIF(False OR True) when the first is False and the 2nd is True. Either way, the IIF evaluates to TRUE, which is probably no good to you.
I don't think you've provided enough data or enough result examples, and as June7 mentioned, the zip folder is empty. Perhaps that would have provided some of the missing stuff, like [pp end] and Result.
Your expression is probably part of a sql statement, in which case a UNION query comes to mind if you can't resolve the expression problem. In other words, you'd get records for an applied date for each pertinent action, then query the max based on those results. I'm not yet convinced you have to resort to that though.
Last edited by Micron; 10-08-2018 at 08:28 PM.
Reason: correction
The more we hear silence, the more we begin to think about our value in this universe.
Paraphrase of Professor Brian Cox.