Opening Balance

Question

Access 2013. A Rookie that put in 10 hours a day since 90 days. Progress good. What is the expression to have an opening balance when I print an account. Many debits and credits happened i.e. till 31Oct2015. Now when I print start date 1Nov to Today. How do I get the opening balance on 1Nov, which is the closing balance of 30 Nov.

Answer 1

Access 2013. A Rookie that put in 10 hours a day since 90 days. Progress good. What is the expression to have an opening balance when I print an account. Many debits and credits happened i.e. till 31Oct2015. Now when I print start date 1Nov to Today. How do I get the opening balance on 1Nov, which is the closing balance of 30 Nov.

Hi Hans,

I cannot connect the 1 Nov. opening balance and the 30 Nov. closing balance.

For these kind of cases I sacrifice a little bit of normalization in favor of easiness and speed. I use a separate table to keep the opening balance and the closing balance per year. For the current year the "closing balance" is the current balance, and for previous years the closing balance is the closing balance of that (financial) year.

I use a small routine to check whether the balances are correct with respect to the mutations, and to check whether the closing balances match the opening balances of the next year.

Imb.

Answer 2

Thanks Imb, what is your name, from where? By choice all my transactions of course are in the tables, and they add up over years. My choice is to not keep closing balances in a table, but I will think some more about it. (I sometimes want to change documents of the past, which is not good for GAAP but good for me. When I print a report and choose a "start date" and "end date". I assume some formula should add all the debits, and subtract all the credits, before Start Date, and open the first line of the print with that. But I assume there is an easy way to do it. Access surprised me with the way some expressions work, and I just try to save time asking help here.

Thanks.

Answer 3

Thanks Imb, what is your name, from where? By choice all my transactions of course are in the tables, and they add up over years. My choice is to not keep closing balances in a table, but I will think some more about it. (I sometimes want to change documents of the past, which is not good for GAAP but good for me. When I print a report and choose a "start date" and "end date". I assume some formula should add all the debits, and subtract all the credits, before Start Date, and open the first line of the print with that. But I assume there is an easy way to do it. Access surprised me with the way some expressions work, and I just try to save time asking help here.

Hi Hans,

There is a kind of formula that can calculate all debits and all credits before any Start Date: look at the Dsum function.

You have to calculate that value each time you want to see the form or report, and each year the calculation will increase. But you are perfectly "normalized" when you want to do that.

Me too, I have sometimes to do some "corrections" in already closed years. That means you have to correct the closing balance of that year. This difference (positive or negative) can then be added to all subsequent opening and closing balances of the following years to make a consistent total again. This last can even be automated.

Imb.

Answer 4

Answer 5

I tried to copy a printed ledger account. I got the opening balance through a sub query in some way, and it is right. The closing balance is also calculated correct, for I added up the opening balance plus all debits less all credits. As you can see the opening balance is not included in the running balance, because of my makeshift solution. There is probably another way of doing the whole thing.

Help!

Answer 6

Closing balance not right.

Answer 7

I tried to copy a printed ledger account. I got the opening balance through a sub query in some way, and it is right. The closing balance is also calculated correct, for I added up the opening balance plus all debits less all credits. As you can see the opening balance is not included in the running balance, because of my makeshift solution. There is probably another way of doing the whole thing.

Help!

Hi Hans,

You use a running balance in the ledger account. In my applications I hardly use any running balances. What counts for me is the Opening balance, the mutations and the Closing balance. And that must match. Why you such have a big difference, I don't know. You can re-examine the algoritm to calculate the Closing balance.

Sometimes I have some running figure in a report, but while I do not use Access reports but generate directly rtf-files, any running figure is not a problem.

In forms I have no control for a running figure, but I have a routine that can produce it if necessary, for instance to check whether that figure sometimes goes negative.

So with the running balance I cannot help you much.

But I saw an other problem. I think you use a Double type for the balances. Double type have always an intrinsic inaccuracy, that can result in inaccurate behaviour. If you look at the last line of your example, then the line value is 60.17,  but 60.18 is added to the balance. For all money related field I use the Currency type. This works excellent for addition or substraction. In case of VAT calculations you still need to do a rounding.

Imb.

 

Answer 8

Thank you. South African currency is Rand and Cents. I don't like the R in front of amounts, but yes I need the rounding, and will follow your advice solving that problem with currency. On the training videos I found guys from UK displaying payments on invoices. I have an accounting degree and they taught me payments shows on a statement and not on an invoice. 

The statement that you send to a customer at the end of the month, that shows any movement. I would like it to look like the example I pasted here. I am used to that all over. Statements from the banks, and other Creditors, most of them look like that. There is an opening balance and running balance. I do not really need a closing balance since the last amount in the running balance is the closing balance, but I believe I've got that if I wanted to do closing balance differently.

Answer 9

My example is still full of things that must improve. Date sorting, Document numbers etc, but right now I focus on getting the opening balance to be part of the running balance

Answer 10

Following what you said. It is part of my plans to enter a closing Journal at the end of every financial year. So once a year is fine, but every month I want to print financials, but mostly because sending statements to customers and reconciling with creditors. Creditors and banks sent me statements with opening balances and running balances.

Answer 11

Thank you. South African currency is Rand and Cents. I don't like the R in front of amounts, but yes I need the rounding, and will follow your advice solving that problem with currency. On the training videos I found guys from UK displaying payments on invoices. I have an accounting degree and they taught me payments shows on a statement and not on an invoice. 

Hi Hans,

If you change amounts from Double (or Single) to Currency type, then you probably will see the Rand sign before the values in your table (as I see the Euro sign).

This is just a display format for the table. In Forms and Reports you can use your own format.

But you can also change the default format in the table. With the table in design mode, go to the Currency field. Then go to General. For the Notation you then use the Standard Notation. Since I only have the Dutch version of Access, I hope I used the right English words.

Imb.

Answer 12

If I speak my mother tongue, Afrikaans, you will probably understand everything I say. So it is 03H37 here. I have changed my Ledger account report so it doesn't have running balances, since the Opening balance calculates correct, as well as the Closing Balance. But I will continue to try to get it the way I like it.

Answer 13

Hi Hans,

>> I have changed my Ledger account report so it doesn't have running balances, since the Opening balance calculates correct, as well as the Closing Balance

Based on your description, it seems your original issue about Opening balance has been resolved. If so, I suggest you mark the helpful reply as answer to close this thread. If not, please feel free to let us know.

Best Regards,

Edward

---

We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
Click HERE to participate the survey.

Answer 14

Edward. Yes I will mark it, but it is not really what I wanted.

Answer 15

Hi Hans,

>> I have changed my Ledger account report so it doesn't have running balances, since the Opening balance calculates correct, as well as the Closing Balance

Based on this, Opening balance calculates correct. I assume your issue has been resolved.

If your issue has not been resolved, or this is not what you want, please feel free to unmark it. And if you have any issues when you achieve your requirement in your like, please feel free to post back.

Best Regards,

Edward

---

We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
Click HERE to participate the survey.