Consolidating the group Trial Balance using VBA open record set (1 Viewer)

[Minty]

Glad you have it working, however you have left in all the very unnecessary brackets that Access uses in it's generated queries, which makes it look 10 times more complex than it needs to be: E.g.
(((dbo.tblEfdReceiptsPOS.InvoiceNumber) IS NOT NULL))
Could simply be
dbo.tblEfdReceiptsPOS.InvoiceNumber IS NOT NULL

Another tip, If you used table aliases you would also reduce your typing considerably, and make things much easier to read.
Your last Union Select for instance could be

SELECT
  tPA.POSDate,
  tPA.BISIDRev,
  tPA.RevenueAccount,
  tPA.RevAcc,
  ((((((tPA.SellingPrice) / (1 + COALESCE(tPA.Tax, 0))))
  * tPA.QtySold) * COALESCE(tPA.FCRate, 0)) * -1) AS Ledger
FROM dbo.tblEfdReceiptsPOS tERPos
INNER JOIN dbo.tblPosAccounts tPA
  ON tERPos.INVID = tPA.SoldID
WHERE tERPos.InvoiceNumber IS NOT NULL

And unfortunatley you didn't change the IIf statements so it still really difficult to see what's happening.

 

[nector]

Ok thank you so much I will work on those small issues.