467,076 Members | 891 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

Post your question to a community of 467,076 developers. It's quick & easy.

Calculating Opening Balance in a Report !

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
  • viewed: 6005
Share:
2 Replies
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
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.

Similar topics

1 post views Thread by jlm | last post: by
2 posts views Thread by bossenb6 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.