Hi...I have been using and modified slightly a sample accounting DB posted by Khawar which has been a great help, I have changed an SQL union query which brought together a client statement with a balance brought forward depending on date criteria selected ...this however is now not working correctly 
if anyone is interested in having a look at the db and possibly helping me sort out the error I would be really grateful...I will post the SQl below but think that without the DB it wont be very clear to say the least !
What the report shul dbe doing is looking at the statrt date of the date criteria selected and reporting those transaction after that date with an opening line 'Balance brought forward' with a sum of all previous debits and credits...
it is now showing the opening line for each transaction before the start date rather than a single opening line ???
many thanks for any help
cheers Fi
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, tblApplication.ClientAppID, tblApplication.TitleFALU, tblApplication.ForenameFALU, tblApplication.SurnameLU,tblApplication.policytypeLU,tblApplication.Add1RFA,tblApplication.Add2RFA,tblApplication.Add3RFA,tblApplication.Add4RFA,tblApplication.Add5RFA,tblApplication.PostCodeRFA,tblApplication.CountryRFALU,VouchersMain.ClientID
FROM ((tblClientMain INNER JOIN tblApplication ON tblClientMain.ClientMainID = tblApplication.ClientIDFK) INNER JOIN VouchersMain ON tblClientMain.ClientMainID = 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, tblApplication.ClientAppID, tblApplication.TitleFALU, tblApplication.ForenameFALU, tblApplication.SurnameLU,tblApplication.policytypeLU,tblApplication.Add1RFA,tblApplication.Add2RFA,tblApplication.Add3RFA,tblApplication.Add4RFA,tblApplication.Add5RFA,tblApplication.PostCodeRFA,tblApplication.CountryRFALU,VouchersMain.ClientID
FROM ((tblClientMain INNER JOIN tblApplication ON tblClientMain.ClientMainID = tblApplication.ClientIDFK) INNER JOIN VouchersMain ON tblClientMain.ClientMainID = 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.Add1RFA,tblApplication.Add2RFA,tblApplication.Add3RFA,tblApplication.Add4RFA,tblApplication.Add5RFA,tblApplication.PostCodeRFA,tblApplication.CountryRFALU,VouchersMain.ClientID,
tblApplication.ClientAppID,"Opening Balance Brought forward",tblAccTranType.CurrentAcct
ORDER BY VDate, VNum;
if anyone is interested in having a look at the db and possibly helping me sort out the error I would be really grateful...I will post the SQl below but think that without the DB it wont be very clear to say the least !What the report shul dbe doing is looking at the statrt date of the date criteria selected and reporting those transaction after that date with an opening line 'Balance brought forward' with a sum of all previous debits and credits...
it is now showing the opening line for each transaction before the start date rather than a single opening line ???
many thanks for any help
cheers Fi
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, tblApplication.ClientAppID, tblApplication.TitleFALU, tblApplication.ForenameFALU, tblApplication.SurnameLU,tblApplication.policytypeLU,tblApplication.Add1RFA,tblApplication.Add2RFA,tblApplication.Add3RFA,tblApplication.Add4RFA,tblApplication.Add5RFA,tblApplication.PostCodeRFA,tblApplication.CountryRFALU,VouchersMain.ClientID
FROM ((tblClientMain INNER JOIN tblApplication ON tblClientMain.ClientMainID = tblApplication.ClientIDFK) INNER JOIN VouchersMain ON tblClientMain.ClientMainID = 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, tblApplication.ClientAppID, tblApplication.TitleFALU, tblApplication.ForenameFALU, tblApplication.SurnameLU,tblApplication.policytypeLU,tblApplication.Add1RFA,tblApplication.Add2RFA,tblApplication.Add3RFA,tblApplication.Add4RFA,tblApplication.Add5RFA,tblApplication.PostCodeRFA,tblApplication.CountryRFALU,VouchersMain.ClientID
FROM ((tblClientMain INNER JOIN tblApplication ON tblClientMain.ClientMainID = tblApplication.ClientIDFK) INNER JOIN VouchersMain ON tblClientMain.ClientMainID = 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.Add1RFA,tblApplication.Add2RFA,tblApplication.Add3RFA,tblApplication.Add4RFA,tblApplication.Add5RFA,tblApplication.PostCodeRFA,tblApplication.CountryRFALU,VouchersMain.ClientID,
tblApplication.ClientAppID,"Opening Balance Brought forward",tblAccTranType.CurrentAcct
ORDER BY VDate, VNum;
