Create report that shows a previous balance as of a date entered on a form

J

julietafrias18

I need to send out statements to the customers, but I need to show the
previous balance as well as the transactions for the range I select.
Table Name: Transactions
Table Fields: TransactionID, TransactionDate, TransactionAmount,
TransactionCustomerID
Transaction Activity:
Customer ID: 30
7/1/09 300.00
7/15/09 400.00
9/25/09 200.00
10/1/09 100.00
12/28/09 100.00

If I pull a statement that will show the transactions from 10/1/09 to
12/31/09, I would like to be able to see that in a report in the following
way:

Previous Balance 900.0
10/1/09 100.00
12/28/09 100.00
Ending Balance 1100.00
 
S

Steve

You can put an unbound textbox in the report header and use the DSum
function in its control source to get the previous balance. Use a query to
get the transactions and put the query in the recordsource of the report.
The ending balance is then the sum of the unbound textbox and the records in
the detail section.

Steve
(e-mail address removed)
 
G

Gina Whipp

julietafrias18,

Somethng like the below as the RecordSource on your report and use
parameters selected from your form.

UNTESTED UNTESTED

SELECT T1.TransactionDate, T1.TransactionAmount, T1.TransactionCustomerID,
DSum("TransactionAmount","Transactions","TransactionID >= " &
[TransactionID]) AS Balance
FROM Transactions AS T1
WHERE (((T1.TransactionDate) Between
[Forms]![frmTransactions]![TransactionDate] And
[Forms]![frmTransactions]![TransactionDate]) AND
((T1.TransactionCustomerID)=[Forms]![frmTransactions]![TransactionCustomerID]))
ORDER BY T1.TransactionDate DESC , T1.TransactionID DESC;


--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm
 
D

Duane Hookom

Hi Gina,
The news groups have been wonky lately. They seem to be better today.
Hopefully we get notification emails.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Calculate Previous Balance as of a date 0
Calculate Start and End Date of a Quarter 11
Balance Forward 4
horizontal & vertical on same report?? 2
Calculate beginning balance, but print details on report 3
Access Opening Balance Expression 25
Calculate interest amount based on average daily balance 1
Force a query to populate consecutive dates? 3

Top