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

Minty

AWF VIP
Local time
Today, 04:01
Joined
Jul 26, 2013
Messages
10,380
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
SQL:
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

Member
Local time
Today, 06:01
Joined
Jan 21, 2020
Messages
388
Ok thank you so much I will work on those small issues.
 

Users who are viewing this thread

Top Bottom