[Sylva]

I am working on a project aimed at tracking daily transactions of Deposits and Withdrawals, similar to the Ledger Sample ledger Database in MS Access. Using a Report Date Range Form to filter transactions for a given period, I want to see the Opening (Beginning) balance on the account before the transactions for the selected Date Range and the Closing (Ending) Balance at the end of the selected Date Range. That is, March Opening Balance = Sum of All Deposits before 1st March less Sum of All Withdrawals before 1st March. Then, March Closing Balance = March Opening Balance + Total Deposits in March - Total Withdrawals in March. My Transaction table looks like this ( sorry, I'm having problems attaching a zipped copy of my DB). The table has four columns: TransDate, Details, Deposit and Withdrawal:

Transaction: TABLE

TransDate Details Deposit(D) Withdrawal(W)
02/02/06 Sales 10,000.00(D)
04/02/06 Purchase 6,000.00(W)
12/02/06 Rent received 2,000.00(D)
06/03/06 Tele Charges 500.00(W)
10/03/06 Sales 5,000.00(D)
23/03/06 Purchase 4,000.00(W)
29/03/06 Sales 1,500.00(D)
08/04/06 Cleaning Expenses 400.00(W)


April Opening Balance = March Closing Balance OR alternatively, April Opening Balance = Sum of All Deposits before 1st April less Sum of All Withdrawals before 1st April. Etc, etc.

I have used Dsum with partial success. Most times, the opening Balance it calculates is the difference between all the DepositAmount and WithdrawalAmount, instead of showing the balance of DepositAmount against WithdrawalAmount for the period preceding the Start Date (or Beginning Trans Date). And if dates in the date values in ReportDateRange Form are not in the Transactions table, the Opening is zero (0). My formula for the text Box OpeningBalance is:


[CODE]=DSum("[Deposit]","Transaction","[TransDate] < #" & Forms!ReportDateRange!txtStartDate & "#")-DSum("[Withdrawal]","Transaction","[TransDate] < #" & Forms!ReportDateRange!txtStartDate & "#")[/CODE]

What formula do I use for the textbox OpeningBalance so that it will give me the correct opening balance at all times?

Could someone out there please come to my rescue and could the gurus at Microsoft please update that sample Database so that Reports will show Opening and Closing Balances. Ledger/Transactions Reports are incomplete if they do not contain balance from previous period.


Sylva.

[farzad_1354]

Hi dear all
This is very simple to work in access
You must add a variance of 2 field in qry
Like [Withdrawal] - [ Deposit] and add this on report then just in property assign in running sum to "Over all"

[Micromax]

Hi, maybe you can put this in the control source for OpeningBalance.
=IIf([TransDate]>#03/31/2006,0,CLng([Deposits]-[Withdrawals])

You may have to tweak it for the form.

Rob