When you use LEFT join you have to account for NULL values using function similar to VFP NVL or MS SQL Server ISNULL. Something like
s.bDrAmt + ISNULL(uf.bDr,0) - ISNULL(uf.bCr.0) AS bBalDr,
>
>I had been working on your code in the mean time. I have narrowed it down to the following;
>
>
SELECT 'Accounts J.V. ' AS cFlag, t.iID, t.cBk, t.iNo, t.dDt,
> s.bDrAmt,
> s.bCrAmt,
> uf.bDr, uf.bCr,
> s.bDrAmt + uf.bDr - uf.bCr AS bBalDr,
> s.bCrAmt + uf.bCr - uf.bDr AS bBalCr
> FROM tAccountsJV t
> JOIN sAccountsJV s
> ON s.iPID = t.iID
> AND s.iAcctID = 485
> LEFT JOIN uOSAccounts uf
> ON uf.iPID = t.iID OR uf.iSID = t.iID
>
>This returns 2 rows but the problem is either bBalDr or bBalCr is right but the other in the same row is wrong dues to the kind of calculation. I tried a IIF() in FireBird but that is not available. Any idea to improve this SQL?
>
--sb--