Expression within Expression issue

[xyba]

Hi

In a query I've created the following two expressions to check if the first field has a specific value and the second field has a date:

Expr12: IIf([Err1]="Levy" And [Err1ResDate] Is Not Null,0,1)

Expr13: IIf([Err2]="Levy" And [Err2ResDate] Is Not Null,0,1)

I have then created a further expression to test where either of the above two expressions meet the criteria:

Expr14: IIf([Expr12]=1 Or [Expr13]=1,1,0)

However, whenever I add criteria (1 or 0) to [Expr14], I get the "Enter Parameter Value Expr12" pop up.

Can anyone tell me, please, why this might be occurring?

Thanks

 

[arnelgp]

that will not work, you have to build
the two expression together.
first we simplify your expressions:

Expr12: ([Err1]="Levy" And [Err1ResDate] Is Not Null)+1
Expr13: ([Err2]="Levy" And [Err2ResDate] Is Not Null)+1
Expr14: Abs((([Err1]="Levy" And [Err1ResDate] Is Not Null)+1) Or (([Err2]="Levy" And [Err2ResDate] Is Not Null)+1))

 

[Minty]

Generally you can't refer to a calculated field in another calculated field in the same query.
You have to use the full syntax so your Expr 14 should be;

IIf(IIf([Err1]="Levy" And [Err1ResDate] Is Not Null,0,1) = 1 OR IIf([Err2]="Levy" And [Err2ResDate] Is Not Null,0,1) = 1,1,0)

 

[xyba]

@ arnelgp and Minty

Thanks guys, sorted and working now.