i want to make a ledger how i show openning balance as start date in access

[Burhan_iqbal]

in table i have debit , credit and balance please kindly tell me how i make a query that i will get an opening balance

[ranman256]

In the query, sum the debit-credit.

[pbaldy]

You can use DSum() to sum transactions that occur prior to the start date of your report. I would not try to store balance.

[Burhan_iqbal]

if i share my database can u help me out how i get the opening balance

[ArviLaanemets]

1. You have a table (tblMovements), where you register all movements with different movement types (incoming, outgoing, correcting, etc.);
2. You have a table (tblInventory), where you register a balance at certain dates (like an inventory table for goods, but it looks like you will works with accounts instead);
3. When you register a new account, you enter the opening balance for it into inventory table.

To calculate a balance at certain date, you find the balance for account in tblInventory with max inventory date before balance date, add all incomes for account from found inventory date until account date, and distract all outgoings for same period.

To find a opening balance for account, you find from inventory table the balance with smallest inventory date for this account.

[Burhan_iqbal]

thanks for reply there is the problem that how i get opening balance in ledger i share the picture also please kindly see and advice me

[pbaldy]

=DSum("Debit - Credit", "TableName", "DateField < #" & FOrms!FormName.StartTextbox & "#")

Using your actual table/field/object names.

[Burhan_iqbal]

thanks for all but its not work for me , can you please help me out for opening balance in crystal report

[pbaldy]

I don't use Crystal, you may want to start a new thread in a more appropriate forum. Or you could explain what "not work" means exactly. Or attach the db here.