Income Stmt Report Question - Microsoft Access Forum


	Back to Access Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

Income Stmt Report Question

Rod Nyberg

2 years ago

Hello. I'm building and Income Statement Trend report with months on top as columns and the rows as accounts. I used a crosstab query as the report source which is laid out in the same format. The Detail section of the report is Acct, Description and months.

Where I'm stuck is in crating row totals that sum selected accounts and row totals that subtract. For example I want to sum RevAcct1+RevAcct2 to make a Gross Revenue row and then take the Gross Revenue for and subtract a Deductions account.

I'm trying to do this as calculated fields but I'm not understanding how. I've searched the 599cd sight as well as internet sites and I've not found anything useful. Thank you for any insight you may have.

	Rod Nyberg OP @Reply 2 years ago

Kevin Yip @Reply

2 years ago

You need to use VBA to do this. Normally, you put the Sum() function in a footer, but Sum() will sum all accounts, which won't work for you because you want to sum select accounts. So you need to use the Detail Format event in VBA. The event is triggered every time the report is about to print a new record. Use VBA to check the account info of that record. If it's an account you want to sum, add its value to a global variable. When all records are processed, the variable will have the total you want.

	Rod Nyberg OP @Reply 2 years ago
Thank you Kevin! I will give that a try.

	Rod Nyberg OP @Reply 2 years ago
I'm assuming the same would be true for a Form version?

Kevin Yip @Reply

2 years ago

For a form, it needs to be done differently.  In fact, this method works for both a report or a form.  Create a recordset object for the form's recordset:

     Dim r as Recordset
     Set r = Me.RecordsetClone

Then you create a recordset loop to check each record's account, and add to the total accordingly.

A third method is to use DMax() to sum select accounts.  E.g.:

     DMax("[Field to be summed]", "Crosstab query name here", "[Account Name] IN ('Surgical Fees', 'Medical...')")

But it's not recommended because it will likely be (much) slower, since DMax() will run the crosstab query a second time.

	Rod Nyberg OP @Reply 2 years ago
Thank you for your response and examples Kevin! Very much appreciated.

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Forum.

Next Unseen

New Feature: Comment Live View