Code:
SELECT IIf(IsNull([tradeid_hp]),"TradeID missing from tblHP",(IIf(IsNull(IIf([portfolio_aam]=[portfolio_hp],Null,"Port,") & IIf(Abs([netamt_aam]-[netamt_hp])<=0.01,Null,"Net,") & IIf(Abs([price_aam]-[price_Hp])<=0.000001,Null,"Price,")),Null,Left((IIf([portfolio_aam]=[portfolio_hp],Null,"Port,") & IIf(Abs([netamt_aam]-[netamt_hp])<=0.01,Null,"Net,") & IIf(Abs([price_aam]-[price_Hp])<=0.000001,Null,"Price,")),Len((IIf([portfolio_aam]=[portfolio_hp],Null,"Port,") & IIf(Abs([netamt_aam]-[netamt_hp])<=0.01,Null,"Net,") & IIf(Abs([price_aam]-[price_Hp])<=0.000001,Null,"Price,")))-1)))) AS FinalNotification, IIf(IsNull([tradeid_hp]),"TradeID missing from tblHP",[finalexpr]) AS FinalBase, IIf([portfolio_aam]=[portfolio_hp],Null,"Port,") & IIf(Abs([netamt_aam]-[net_hp])<=0.01,Null,"Net,") & IIf(Abs([price_aam]-[price_Hp])<=0.000001,Null,"Price,") AS BaseExpr, IIf(IsNull([baseexpr]),Null,Left([baseexpr],Len([baseexpr])-1)) AS BuildExpr, IIf(IsNull(IIf([portfolio_aam]=[portfolio_hp],Null,"Port,") & IIf(Abs([netamt_aam]-[netamt_hp])<=0.01,Null,"Net,") & IIf(Abs([price_aam]-[price_Hp])<=0.000001,Null,"Price,")),Null,Left((IIf([portfolio_aam]=[portfolio_hp],Null,"Port,") & IIf(Abs([netamt_aam]-[netamt_hp])<=0.01,Null,"Net,") & IIf(Abs([price_aam]-[price_Hp])<=0.000001,Null,"Price,")),Len((IIf([portfolio_aam]=[portfolio_hp],Null,"Port,") & IIf(Abs([netamt_aam]-[netamt_hp])<=0.01,Null,"Net,") & IIf(Abs([price_aam]-[price_Hp])<=0.000001,Null,"Price,")))-1)) AS FinalExpr, qryFORMAT_AAM.PORTFOLIO_AAM, qryFORMAT_HP.Portfolio_HP, qryFORMAT_HP.TradeID_HP, qryFORMAT_AAM.TRADEID_AAM, IIf(IsNull([NetAMT_AAM]),0,[NetAMT_AAM]) AS Net_AAM, IIf(IsNull([NetAmt_HP]),0,[NetAmt_HP]) AS Net_HP, qryFORMAT_AAM.PRICE_AAM, qryFORMAT_HP.Price_HP, Abs([price_aam]-[price_hp]) AS Expr1
FROM qryFORMAT_AAM LEFT JOIN qryFORMAT_HP ON qryFORMAT_AAM.TRADEID_AAM = qryFORMAT_HP.TradeID_HP
WHERE (((IIf(IsNull([tradeid_hp]),"TradeID missing from tblHP",(IIf(IsNull(IIf([portfolio_aam]=[portfolio_hp],Null,"Port,") & IIf(Abs([netamt_aam]-[netamt_hp])<=0.01,Null,"Net,") & IIf(Abs([price_aam]-[price_Hp])<=0.000001,Null,"Price,")),Null,Left((IIf([portfolio_aam]=[portfolio_hp],Null,"Port,") & IIf(Abs([netamt_aam]-[netamt_hp])<=0.01,Null,"Net,") & IIf(Abs([price_aam]-[price_Hp])<=0.000001,Null,"Price,")),Len((IIf([portfolio_aam]=[portfolio_hp],Null,"Port,") & IIf(Abs([netamt_aam]-[netamt_hp])<=0.01,Null,"Net,") & IIf(Abs([price_aam]-[price_Hp])<=0.000001,Null,"Price,")))-1))))) Is Not Null));
I left several calculation fields in there so you can see how I built the expressions