Report Grand Totals

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

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

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.

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.

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

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.

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

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

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?

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.

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.

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

Select all 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

Select all Open in new window

for last test try below again:
Me!txtBalance = Nz(Me![txtTblIncome]) - Nz(Me![txtTblExpenses])

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

Select all 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

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

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.

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.

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 ....

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.

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

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

Thanks