Hi
Hopefully this is legible
SELECT VouchersMain.VNum, VouchersMain.VDate, tblAccTranType.Account,tblAccTranType.CurrentAcct,VoucherDetail.txtNarration,IIf([CurrentAcct]="Debit",[TransactionAmount],0) AS Debit, IIf([CurrentAcct]="Credit",[TransactionAmount],0) AS Credit, [Credit]-[Debit] AS Balance, IIf([CurrentAcct]="Credit"And[UnitPrice]>0,-[TransactionAmount]/[UnitPrice],0) AS CRAddUnits,IIf([CurrentAcct]="Debit"And[UnitPrice]>0,[TransactionAmount]/[UnitPrice],0) AS DRAddUnits,tblApplication.ClientAppID, tblApplication.TitleFALU, tblApplication.ForenameFALU, tblApplication.SurnameLU,tblApplication.policytypeLU,tblApplication.policyLU,tblContactMain.Address1,tblContactMain.Address2,tblContactMain.Address3,tblContactMain.Address4,tblContactMain.Address5,tblContactMain.PostCode,tblContactMain.CountryIDLU,VouchersMain.ClientID
FROM ((tblContactMain INNER JOIN tblApplication ON tblContactMain.ContactMainID = tblApplication.ClientIDFK) INNER JOIN VouchersMain ON tblContactMain.ContactMainID = VouchersMain.ClientID) INNER JOIN (tblAccTranType INNER JOIN VoucherDetail ON tblAccTranType.AccId = VoucherDetail.AccountId) ON VouchersMain.VNum = VoucherDetail.VNum
WHERE (((VouchersMain.VDate) Between [forms]![frmAccountLedger]![FDate] And [Forms]![FrmAccountLedger]![TDate]) AND ((VouchersMain.ClientID)=[forms]![frmClientPolicyLookUp]![ClientMainID]))
UNION SELECT "" AS VNum, [forms]![frmAccountLedger]![fdATE] AS VD, tblAccTranType.Account, tblAccTranType.CurrentAcct,"Opening Balance Brought forward" AS txtNarration, Sum(IIf([CurrentAcct]="Debit",[TransactionAmount],0)) AS SumOfDebit, Sum(IIf([CurrentAcct]="Credit",[TransactionAmount],0)) AS SumOfCredit, Sum([Credit]-[Debit]) AS Balance, Sum(IIf([CurrentAcct]="Credit"And[UnitPrice]>0,[TransactionAmount]/[UnitPrice],0))AS SumOfCRAddUnits,Sum(IIF([CurrentAcct]="Debit"And[UnitPrice]>0,-[TransactionAmount]/[UnitPrice],0)) AS SumOfDRAddUnits, tblApplication.ClientAppID, tblApplication.TitleFALU, tblApplication.ForenameFALU, tblApplication.SurnameLU,tblApplication.policytypeLU,tblApplication.policyLU,tblContactMain.Address1,tblContactMain.Address2,tblContactMain.Address3,tblContactMain.Address4,tblContactMain.Address5,tblContactMain.PostCode,tblContactMain.CountryIDLU,VouchersMain.ClientID
FROM ((tblContactMain INNER JOIN tblApplication ON tblContactMain.ContactMainID = tblApplication.ClientIDFK) INNER JOIN VouchersMain ON tblContactMain.ContactMainID = VouchersMain.ClientID) INNER JOIN (tblAccTranType INNER JOIN VoucherDetail ON tblAccTranType.AccId = VoucherDetail.AccountId) ON VouchersMain.VNum = VoucherDetail.VNum
WHERE (((VouchersMain.VDate)<[forms]![frmAccountLedger]![FDate]))
GROUP BY"","",tblAccTranType.Account,tblApplication.TitleFALU,tblApplication.ForenameFALU,tblApplication.SurnameLU,tblApplication.policytypeLU,tblApplication.policyLU,tblContactMain.Address1,tblContactMain.Address2,tblContactMain.Address3,tblContactMain.Address4,tblContactMain.Address5,tblContactMain.PostCode,tblContactMain.CountryIDLU,VouchersMain.ClientID,
tblApplication.ClientAppID,"Opening Balance Brought forward",tblAccTranType.CurrentAcct
ORDER BY VDate, VNum;
this is the union query behind the report..thanks again for looking..Fi