Accounting Report

[pcdaveh]

I'm trying to reproduce a report that has an Opening or Beginging Balance and then transactions for a month then a Total / Ending Balance. The Ending Balance for a given month will be the Begining balance for the next month. I can't get the Enging Balance to carry into the next month. This is a report and this is how I need it to look:


TransDate Begining Balance Debit Credit Ending Bal
4/5/2001 18,000 0.00
4/15/2001 16,000 0.00
Month Total -50,000 34,000 0.00 -16,000

5/1/2001 24,000 0.00
5/14/2001 24,000 0.00
Month Total -16,000 48,000 0.00 32,000

You can see above how the Ending balance of -16,000 is carried into the Begining balance column in the month of May. Currently the -50,000 populates that position troughout the report. How do you move the ending balance to the begining balance?

 

[terubok]

I am interested in the solution to your problem as I have a similar problem. I hope somebody can help, too.

Regards.

 

[danvlas]

You could try another approach. Instead of 'carrying' the data through the report, calculate the values on every occurence:

BeginningBalance=DSum(&quot;DebitField&quot;,&quot;YourTableName&quot;,&quot;TransactionDate<=#&quot; & Format(YourDateField - Day(YourDateField),&quot;Medium Date&quot - (&quot;CreditField&quot;,&quot;YourTableName&quot;,&quot;TransactionDate<=#&quot; & Format(YourDateField - Day(YourDateField),&quot;Medium Date&quot

It returns the difference between TotalDebit until the end of previous month and TotalCredit until the end of previous month.

EndingBalance = DSum(&quot;DebitField&quot;,&quot;YourTableName&quot;,&quot;TransactionDate<=#&quot; & Format(DateAdd(&quot;m&quot;,1,YourDateField) - Day(YourDateField),&quot;Medium Date&quot - (&quot;CreditField&quot;,&quot;YourTableName&quot;,&quot;TransactionDate<=#&quot; & Format(DateAdd(&quot;m,1,YourDateField) - Day(YourDateField),&quot;Medium Date&quot

It returns the difference between TotalDebit until the end of displayed month and TotalCredit until the end of displayed month.

Hope this helps,

Dan

 

[danvlas]

OOOOOOOPS...

BeginningBalance=DSum(&quot;DebitField&quot;,&quot;YourTableName&quot;,&quot;TransactionDate<=#&quot; & Format(YourDateField - Day(YourDateField),&quot;Medium Date&quot & &quot;#&quot - DSum(&quot;CreditField&quot;,&quot;YourTableName&quot;,&quot;TransactionDate<=#&quot; & Format(YourDateField - Day(YourDateField),&quot;Medium Date&quot & &quot;#&quot
It returns the difference between TotalDebit until the end of previous month and TotalCredit until the end of previous month.

EndingBalance=DSum(&quot;DebitField&quot;, &quot;YourTableName&quot;,&quot;TransactionDate<=#&quot; & Format(DateAdd(&quot;m&quot;,1,YourDateField) - Day(DateAdd(&quot;m&quot;,1,YourDateField)),&quot;Medium Date&quot & &quot;#&quot-DSum(&quot;CreditField&quot;,&quot;YourTableName&quot;,&quot;TransactionDate<=#&quot; & Format(DateAdd(&quot;m&quot;,1,YourDateField) - Day(DateAdd(&quot;m&quot;,1,YourDateField)),&quot;Medium Date&quot & &quot;#&quot




HTH,

Dan