Iif multiple issue

[Trevor G]

I am not sure if this is the correct syntex as I am receiving errors, what I am trying to achive is to check policy numbers in to different systems to see if they match, and also having to check the year to see they both have the same year in the 2 systems, if both parts match then show a field contents called MI_Ref else show nothing. I have the following so far

Expr1: IIf([ztbl_PI_Prog_Ref_Char.POLICY REF]=[qryPI_July_to_Sept_from Comb.POLICY REF] , IIf([UWYEAR]=[Underwriting Year],[MI_Reference]),"")

I get the #Error or if I change it to another operator I see nothing

 

[namliam]

If you have nested IIFs or nested anything it is usually a good idea to view them nested like so:

IIf([ztbl_PI_Prog_Ref_Char.POLICY REF]=[qryPI_July_to_Sept_from Comb.POLICY REF] 
   , IIf([UWYEAR]=[Underwriting Year]
        ,[MI_Reference]
        )
   ,""
   )

That exposes perhaps something,though mostly logical problems.
Also if you want to use [] you have to use them around each name seperatly thus not:
[ztbl_PI_Prog_Ref_Char.POLICY REF]
But
[ztbl_PI_Prog_Ref_Char].[POLICY REF]

That will likely solve your Syntax problem

 

[Trevor G]

Unfortunately i am still getting the error,

This is the expression I am trying to use
Result: IIf([ztbl_PI_Prog_Ref_Char.POLICY REF]=[qryPI July to Sept from Comb.POLICY REF],IIf([UWYEAR]=[Underwriting Year],[MI_Reference]),"")

I am wondering if there are any issues with database types, would that cause any issues?

Would I be able to apply the same type of function by using switch?

Thank you for your suggestion though.

 

[Trevor G]

Sorted I had 2 different data type netries, one texst one number so they coldn't work, I have adjusted the temp tbale to use a number field and it is working fine.

Again thank you