By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,412 Members | 2,896 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,412 IT Pros & Developers. It's quick & easy.

Calculating Opening Balance in a Report !

P: n/a
I have 2 tables ...

Customer_Master: Cust-Code, Cust_Name

Customer_Transactions: Cust_Code, Date, Details, Debit, Credit

I would like to generate a report in the foll manner, say from 15.1.06
to 15.02.06 :
Customer Code: 999-9876543
Customer Name: abc-xyx-abc

Date Details Debit
Credit
======= ===================== ======== ========
Op.Bal as at 15.1.06 0.00
78.94

15.1.06 Transaction 1
16.1.06 Transaction 2
upto
15.2.06 Transaction Last

Cl.Bal as at 15.2.06 ======== ========

Could someone please tell me how to go about generating this.

Should I use a query or use the table itself as the record source of
the report ?

Most importantly ... how to generate the OPENING BALANCE ? This really
beats me !
Thx & Best Rgds,
Prakash.

Feb 8 '06 #1
Share this Question
Share on Google+
2 Replies


P: n/a
Prakash wrote:
I have 2 tables ...

Customer_Master: Cust-Code, Cust_Name

Customer_Transactions: Cust_Code, Date, Details, Debit, Credit

I would like to generate a report in the foll manner, say from 15.1.06
to 15.02.06 :
Customer Code: 999-9876543
Customer Name: abc-xyx-abc

Date Details Debit
Credit
======= ===================== ======== ========
Op.Bal as at 15.1.06 0.00
78.94

15.1.06 Transaction 1
16.1.06 Transaction 2
upto
15.2.06 Transaction Last

Cl.Bal as at 15.2.06 ======== ========

Could someone please tell me how to go about generating this.

Should I use a query or use the table itself as the record source of
the report ?

Most importantly ... how to generate the OPENING BALANCE ? This really
beats me !
Thx & Best Rgds,
Prakash.

You can use a table or query. Whatever floats your boat. Oftentimes
people use queries since data may come from several tables to make up a
"report" record.

When you open the report, you can filter it.
Dim datFrom As Date
Dim datTo As Date
'i don't use international dates. I use mm/dd/yyyy, you can
'test for yourself
datFrom = #15.1.06#
datTo = #15.02.06#

Docmd.OpenReport "MyReport",,,"TXDate Between #" & _
datFrom & "# And #" & datTo & "#"
This will filter your report to only show records in the time frame.

Usually a report is called from a form. Let's say the form is called
RptForm and you have a from/to date field. For your opening balance you
can use DSum()
=Dsum("Debit","table","TXDate < #" & Forms!RptForm!FromDate & "#") -
Dsum("Credit","table","TXDate < #" & Forms!RptForm!FromDate & "#")

This subtracts the credits from debits for all records in the table less
than the from date in the report form. So create a field to display the
balance and use the above Dsum() statement as your control source...with
modifications to meet your criteria.

These are simply concepts. Hopefully you can adjust to suit your needs.
Feb 8 '06 #2

P: n/a
Thx ! I'll certainly give it a bash & get back to you on the result.
Sounds easy enough ... if i have any further queries I'll post them.

Best Rgds,
Prakash.

Feb 9 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.