Prakash wrote:
I have 2 tables ...
Customer_Master : Cust-Code, Cust_Name
Customer_Transa ctions: 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.OpenRepor t "MyReport",,,"T XDate 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!F romDate & "#") -
Dsum("Credit"," table","TXDate < #" & Forms!RptForm!F romDate & "#")
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.
