Report Grand Totals

I have two Access 2013 tables:  Income & Expense.  I have created a report that will give me totals for each but I can not figure how to get a net balance from it
Richard WilliamsAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mike EghtebasDatabase and Application DeveloperCommented:
new balance per row?

Or, net balance of totals?

I think you want the second one. If so, in the on format event of the area you want to have txtBalance and have:

Me!txtBalance  = Me!txtTotalIncome - Me!txtTotalExpense

Me!txtBalance is an unbound text box.

Also try butting in the control source of txtBalance  the following:

 = Me!txtTotalIncome - Me!txtTotalExpense
0
Richard WilliamsAuthor Commented:
I think you want the second one. If so, in the on format event of the area you want to have txtBalance and have:

Sorry, but a step is missing.  Do I open a report form and put your suggestion somewhere on the form, not sure how, or do I put it in a table or where?   I tried adding it to a report but it did not work
0
Mike EghtebasDatabase and Application DeveloperCommented:
re: > Do I open a report form and put your suggestion somewhere on...

You have either Report object or Form object not both at the same time for this purpose.

Add a text box (in design view of your Report object) where you want the balance number to show up. Name this text box txtBalance.

In the control source of this text box add:

= txtTotalIncome - txtTotalExpense

Here I have assumed your income and expense figures on the report get displayed in text boxes named txtTotalIncome and txtTotalExpense, respectively. If make sure to change the above line to whatever you have.

Try this method first to see if it works before we start using VBA,

Mike
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Richard WilliamsAuthor Commented:
Thanks.  Result:  when I run the report I get either "Name?" or the box  is blank.   It also assumes it has a parameter as a parameter box appears waiting for me to enter information.   I click OK and get the above.    I put in the following:     =txtTblIncome.Transamt - txtTblExpenses.Transamt     which Access immediately puts the table and field name in brackets.
0
Mike EghtebasDatabase and Application DeveloperCommented:
force it to be

=nz([txtTblIncome]) - nz([txtTblExpenses])

btw, what are the names of text boxes showing expense and income numbers?

Could you have your report in design view and upload screen shot of it?

I want to see the location(s) of txtTblIncome, txtTblExpenses, and txtBalance text boxes.
0
Richard WilliamsAuthor Commented:
OK, this last time where you had me put "nz" I get a  0 (zero) in the txtbalance box.

Richard
Access-Picture-2.jpg
Access-Picture-1.jpg
0
Mike EghtebasDatabase and Application DeveloperCommented:
While the property sheet is open, select report footer area (in design view). The property sheet on the top should also say Report Footer. Now, click on Event tab on the property sheet. Double click in "On Format" event. click on [...] on the far right of it. This will take you to OnFormat event where you will put the following code:

me!txtBalance =nz(me![txtTblIncome]) - nz(me![txtTblExpenses])

but make sure to clear the control source of txtBalance. Then save and test it.
0
Richard WilliamsAuthor Commented:
I carefully found each component and entered the following:

Private Sub ReportFooter_Format(Cancel As Integer, FormatCount As Integer)

Me!txtBalance = Nz(Me![txtTblIncome]) - Nz(Me![txtTblExpenses])

End Sub

I left the text box balance in place and removed the formula.

Result:  total is still blank
0
Mike EghtebasDatabase and Application DeveloperCommented:
Ok, here we need to do some test. Copy and paste the following portion in bold:

Private Sub ReportFooter_Format(Cancel As Integer, FormatCount As Integer)

  MsgBox "The income is: " & Nz(Me![txtTblIncome]) & vbnewline & _
                  "The expense is: " & Nz(Me![txtTblExpenses])
    Me!txtBalance = 55 'Nz(Me![txtTblIncome]) - Nz(Me![txtTblExpenses])


End Sub

Let me know what you get.

Mike
0
Richard WilliamsAuthor Commented:
I copied/pasted the bold and saved it.  When I ran the report, the total box is blank once again.   Above you had me:     but make sure to clear the control source of txtBalance. Then save and test it.     The box remains but there is no code in it as you said to clear it.   Do I need to put something there?
0
Mike EghtebasDatabase and Application DeveloperCommented:
Please change the event:

Private Sub Detail_Paint()
    Me!txtBalance = Nz(Me![txtTblIncome]) - Nz(Me![txtTblExpenses])
End Sub

Note that it is using Detail not footer. And the event name is Paint not OnFormat.
0
Richard WilliamsAuthor Commented:
I did and now I get an Error 2465 saying that it cannot find one of the tables and I have had to reboot my computer twice to get out of an endless error loop.    I will delete this access file and open up my last backup file.    I will make a new copy of this db and try once again.

Don't know where you are, but it is 8:06 pm in Oregon.    I will leave another message tonight probably.
0
Mike EghtebasDatabase and Application DeveloperCommented:
I am in Los Angeles. This line of code shouldn't cause that kind of error. To be fair, lets say 50% this line is the cause and 50% some other condition is causing it.

test 1:
Private Sub Detail_Paint()
    ' this is the line ought to work temporarily removed.
    'Me!txtBalance = Nz(Me![txtTblIncome]) - Nz(Me![txtTblExpenses])
   
    ' this line is to test to see if the code could insert some value to Balance text box
    Me!txtBalance = 55
End Sub

Open in new window


test 2, if one works:
Private Sub Detail_Paint()
    ' this is the line ought to work temporarily removed.
    'Me!txtBalance = Nz(Me![txtTblIncome]) - Nz(Me![txtTblExpenses])
   
    ' this line is to test to see if the code could insert some value to Balance text box
    Me!txtBalance = Nz(Me![txtTblIncome])
End Sub

Open in new window


for last test try below again:
Me!txtBalance = Nz(Me![txtTblIncome]) - Nz(Me![txtTblExpenses])
0
Mike EghtebasDatabase and Application DeveloperCommented:
BTW, I have tested this solution and it did work for me. You may also want to try:

Private Sub Detail_Paint()
    ' this is the line ought to work temporarily removed.
   On Error Resume Next
    Me!txtBalance = Nz(Me![txtTblIncome]) - Nz(Me![txtTblExpenses])
   
End Sub

Open in new window


Explanation:  The values in txtTblIncome and txtTblExpenses maybe not available when the code runs thus causing some errors. And, because some events run a few times, on the last try, these boxes are bound to receive some values and all should work fine.

In my test version, I have =5 and =4 in the control sources of txtTblIncome and txtTblExpenses. So, they always had some value in them but in your case, these boxes don't have the necessary values and they need to be calculated perhaps.

Mike
0
Richard WilliamsAuthor Commented:
I have copied all of your tests above to a word processor file so I can close my browser, and will try each one today.    One piece I am not clear about.  You asked me to clear the contents in the text box which exists in my footer where the total balance should be displayed.  So, what effect does entering your code in the various property locations have to do with getting a total to show up in the footer?

I will try each of your examples today in the afternoon and will get back to you.  Perhaps, I will see a result in the text box.  

Thank you
0
Mike EghtebasDatabase and Application DeveloperCommented:
re:> You asked me to clear the contents in the text box which exists in my footer where the total balance should be displayed.

There are two ways you can show a value in a text box like txtBalance in the footer:

1. Have to value in the control source property of txtBalance like
= 5     ' this will show 5 in txtBalance when you run the report, or
= Nz(Me![txtTblIncome]) - Nz(Me![txtTblExpenses]) ' this will insert the result in txtBalance

2. Using VBA. In this case, the control source property has to be empty (cleared). It has to be unbound in another word.
When the event fires, the code want to insert the result obtained from Nz(Me![txtTblIncome]) - Nz(Me![txtTblExpenses]) into txtBalance. This requires txtBalance to be unbound.
0
PatHartmanCommented:
Please forgive me if you have already discussed this.  I got lost reading the thread.

Grand totals go in the report footer.  In the footer, you will have two controls already.  One to sum expenses and the other to sum income.

=Sum(Expenses)

=Sum(Income)

The third box can be:

= Nz(txtSumIncome,0) - Nz(txtSumExpenses,0)

Or
=Sum(Income) - Sum(Expenses)

The only time you can refer to a control name in an expression is if the control is in the current section.  Otherwise, you must repeat the calculation.
0
Richard WilliamsAuthor Commented:
I may give up & go back to ddhsoftware's handbase which I used for over 15 years.    It turns out the 3 personal databases that I thought were working, do not and I am tired of dealing with it.   Thanks to you both.   For several years I programmed in the cp/m and dos days with dBase II, III, & IV, but it really never made it to windows.   It has been several years since I tried to do any programming and have struggled with Access.   I really need to get my 4 db done with your combined comments being db #4 and I have spent way too many hours on this.   If successful I had planned to share it some some friends and agents but not now.    Thanks anyway.   I will mark this complete which it is not since ....
0
PatHartmanCommented:
I am sorry you are so discouraged.  Without seeing the database, it can be difficult to diagnose a problem.  I would probably not have created separate tables for income and expenses if this is an accounting system.  Generally, they can be stored in the same table and the chart of accounts would distinguish them.  It could be that the problem is occurring because the two are on separate subreports and that is harder to net than if everything is in the same recordset.  If you can post a stripped down version of the database, we can probably help more.  Only a few records are probably necessary to identify the problem.

Personally, I have used several other desktop database engines and Access is head and shoulders better than any other option on the market within the past 20 years.  It is a true relational database as well as being object oriented.  That makes it work differently from the other databases such as dBase (which is the one I am most familiar with).  It is a mind-set.  I came to Access from COBOL and adjusting to event driven logic was traumatic in the beginning and even now I sometimes would prefer the more linear approach of a third-generation language.  But all-in-all, Access is a superior RAD tool once you get comfortable with events and functions and property settings and queries.
0
Richard WilliamsAuthor Commented:
I am a free member and can only ask one question. However, I created a prayer db where I could put individuals in one db and requests in another.   I thought it worked, but discovered that the relationship between the two tables did not link the person with a given request.  I tried changing the linkage from "ID" to "ID & person, to person to request.   I tried every conceivable option available, yet the requests db still would not display the individual;  instead it displayed numbers.

As for the financial one, my son asked me to create an Access db to manage his checkbook.  He does not want to use a spreadsheet.  I have been able to create an income and expense table and to show income query & reports but linking them together, none of the previous suggestions have allowed me to see a net balance.    While simple in Excel, not so it seems.     If you wanted to see a strip down of one of my db I can upload it with sample data I suppose.     I have reviewed Access 2013 Bible & other tools, & have downloaded various templates.   But, either I am missing a key ingredient or it is working the way Access wants and I do not see it.   Let me know if you want one or two db & I can upload them, but since I am not yet a paid member, I do not know if that is an option.    Thanks
0
Richard WilliamsAuthor Commented:
Pat, I have joined for 3 months so I can ask more questions.  Regarding my other 3 db projects, I asked a question and received a good solution the same day which has worked just fine.  So now I am back to the check register db.  I am thinking of 3 approaches.   The First is akin to your suggestion of using 1 table.  In it, I have set the Totals button to the bottom of the table and when you add credits or expenses it shows you a net total.  This is akin to a spreadsheet.  The Second  uses one table and is designed around the "Source" which is either the source of the credit (i.e. payroll, etc.) or payee (i.e. Safeway, Costco).   The Third is the one that this stream of comments has been about, which is built around two tables, one for income and one for credits.

Consequently, if you would, give me an brief idea how you would use one table.   I can see where one table would probably work.  So would it be like Option 1 above where you enter data in a spreadsheet like fashion (which I can see being done in the table or a form), or something else.

Thanks
0
Richard WilliamsAuthor Commented:
Thanks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.