Hi Paul,
That's a lot of left joins! Have you tried using NVL() in your query? Maybe it will cause the SQL engine to operate differently.

Good luck!

>I haven't found the cause yet, but this select statement fails.
>With an IIF() referenceing three tables, it doesn't return the values for one of the IIF branches.
>
>I get BusinessName values returned, but blank spaces for the Firstname+Surname.
>If I add to the select the cFirstname and cSurname fields, they are populated correctly.
>
>I think it's to do with the way VFP pre-evaluates the first record, because the first record in the result set is for a business (and they work) it seems to ignore the relationship to Person and only resolve the relationship to CommercialProfile.
>
>

>SELECT ch.cEuphonyID ;
>      ,PADR(IIF(EMPTY(co.BusinessEntityFK) ;
>           ,ALLTRIM(pe.cFirstname)+" "+ALLTRIM(pe.cSurname) ;
>           ,ALLTRIM(cp.cBusinessName)) ;
>           ,50) as Name ;
>      ,pe.cEmail ;
>      ,pe.cTel1 ;
>      ,ad.cAddr1 ;
>      ,ad.cAddr2 ;
>      ,ad.cAddr3 ;
>      ,ad.cAddr4 ;
>      ,ad.cAddr5;
>      ,ad.cPostcode ;
>      ,su.cEupRegn ;
>      ,TTOD(su.tStart) dStart ;
>  FROM UniConn!ConsultancyHdr ch ;
>  LEFT JOIN UniConn!Subscription su ;
>       ON su.SubscriptionPK = ch.SubscriptionFK ;
>       AND lActive = .t. ;
>  LEFT JOIN UniConn!Contact co ;
>       ON co.SubscriptionFK = ch.SubscriptionFK ;
>       AND lDefault = .t. ;
>       LEFT JOIN UniConn!Person pe ;
>            ON pe.PersonPK = co.PersonFK ;
>       LEFT JOIN UniConn!ucAddress ad ;
>            ON ad.AddressPK = co.AddressFK ;
>       LEFT JOIN UniConn!BusinessEntity be ;
>            ON be.BusinessEntityPK = co.BusinessEntityFK ;
>            LEFT JOIN UniConn!CommercialProfile cp ;
>                 ON cp.CommercialProfilePK = be.CommercialProfileFK ;
>  INTO CURSOR ucActiveBCs ;
>       NOFILTER
>