Running Balance 1

[CoolFactor]

I have two fields with following titles Deposits and Contributions that both store positive amounts. Initially there was an amount that carried over from the previous year. What I need is the differences from the Deposits and Contributions fields against the amount that was carried over from last year. I've built a report with just the simple sums of both these fields group by Month. In the design view of date field of the query that I built for this report, I included an expression so I can have parameterized date range report. I want to include a text box in a month header that I created that includes differences from the Deposits and Contributions based on the initial amount carried over from the previous year on a monthly basis. Any ideas will be greatly appreciated.

 

[PaulBricker]

I have not tried this, but if you are looking for ideas, you might try getting the beginning balance into a Public(Global) variable and then when you open the report, use a procedure in the Open event that looks at that value and assigns it to a control(visible or not)in your report. From there you may be able to use the running sum property of a control to get the values the way you want.

You will probably have to use some type of conditonal statement to get the first months value and the balance added together, but that shouldn't be real difficult.

Post back with specific question.

Paul

 

[PaulBricker]

I did have a chance to test it and it seems to do what you need. Here is what I did.

In a module I put

Public gdblRunSum as Double

Public Function RunSum()
RunSum = gdblRunSum
End Function

On a form, I put a control that used DLookUp() to get a value from a table(essentially your balance), and added a button. In the click event for the button I put.

gdblRunSum = Me.Text0
DoCmd.OpenReport "tblVehicleCosts", acViewPreview

Then on the report I added three controls. The first one in the Report Header had a control source that was
=RunSum(). The name of this control was [BalanceValue]
This brought the Balance to the Report.
In the second control (in the GroupHeader) I set the control source to =1, the running sum property to "Over All" and the visible property to No. The name of this box was Text19.
The third control had a control source that said

=IIf(Text19 = 1,[BalanceValue] + [MonthlyValue],[MonthlyValue])

where [MonthlyValue] is my Monthly total that I want to get a running sum for.

The Running Sum property for [MonthlyValue] was set to Over All.

You may have to modify a few things, but it did give me the rusults I think you are looking for.

Paul

 

[CoolFactor]

Thanks Paul Bricker for the Running Balance solution.

 

[CoolFactor]

Let me give you a better Idea of what I am looking for though. I created this excel spreadsheet to give you exactly what I want to accomplish in Access.
Example:
12-31-05 Balance Forwarded: $30,000
Date Deposits Contributions
1-1-06 $2000 $1000
1-15-06 $1000 $1000
January Balance: $31,000
Balance Forwarded: $31,000
Date Deposits Contributions
2-1-06 $5000 $2000
2-23-06 $4000 $1000
2-28-06 $3000 $1000
February Balance: $39,000
Balance Forwarded: $39,000
and it continues for each month

As you can see I'm subtracting the Contributions from the
Balance Forwarded and adding the Deposits to it in order to get the Ending Balance for that month. Then that balance is carried over to the next month.

I want to do this exact same thing in an Access report. What I did in Access thus far is made a table with the Date , Deposits, Contributions and other related fields. The initial deposit that I started with in this Access table was
the $30,000 that was forwarded from 12-31-05 Balance Forwarded. I created a parameterized date report grouped by month. When I enter the parameters for the Start Date and End Date for October for example what happens is the text box I created in the Month Header to account for this end of the month balance is just taking the difference between the Deposits and Contributions instead of the initial deposit of $30,000 and the rest of the previous deposits and contributions.

An example of what is happening is this:
Lets just take the excel spreadsheet I used as an example:
I'll pick the month February:
What's happening in the Access Report is that it is giving me the difference between Deposits and Contributions which would give you the dollar amount of $8000. What I need is dollar amount of $39,000 which is the correct ending February Balance.

In this Report I created parameterized query that would give me the deposits and contributions for that month or for several months depending on the parameters that I enter. The text box that I created in the month header of the report would contain the expression that would produce the calculation that I am looking for.

 

[PaulBricker]

Sorry for the delay is getting back to you CoolFactor. I was away for the Holidays. The real issue is getting the parameter to work. I will have a look at it today and see what I can come up with.

Paul

 

[PaulBricker]

Here is what I did.
1. I created a query with this SQL

SELECT [b]DateSerial(Year([Date]),Month([Date])+1,0)[/b] AS myDate, Sum(tblBalance.Deposits) AS SumOfDeposits, Sum(tblBalance.Contributions) AS SumOfContributions
FROM tblBalance
GROUP BY DateSerial(Year([Date]),Month([Date]),1)
HAVING (((DateSerial(Year([Date]),Month([Date]),1))>=[Forms]![frmParameter]![Text0]))
ORDER BY DateSerial(Year([Date]),Month([Date]),1);

The DateSerial() function just gives me the last day of the month so that all the totals are grouped on each month. The only issue might be that my first entry in the table was for Dec 31, 2005 for $30000. I had to have a starting point, and that was it for me. The rest of the entry were just the same as your spreadsheet example.

2. Then I created this query.

SELECT Sum(tblBalance.Deposits) AS SumOfDeposits
FROM tblBalance
WHERE (((DateSerial(Year([Date]),Month([Date])+1,0))<[Forms]![frmParameter]![Text0]));

The first query is a parameter query that my report is built on. The second query is a paramter query that gives me a balance to start with.

3. I have a form, frmParameter, that has 2 textboxes on it. One for entering a start date to filter the first query, and one for passing the value from the second query to my global variable. I have a button that uses DLookup() to get the value from my second query, pass it to my global variable and open the report. This is the click event

Private Sub Command2_Click()
Me.Text3 = DLookup("SumOfDeposits", "qryBalance2")
DoCmd.OpenReport "rptBalance", acViewPreview
gdblRunSum = Me.Text3
End Sub

4. On the report, which has qryBalance as the Record Source, I have a textbox to grab the global variable in the Page Header(as I described above). I have a Date header, with the date field in it, that groups the values into months.
I have three textboxes in a SumOfDeposits Header. Two,SumOfDeposits, and SumOfContributions have their Running Sum properties set to Over All, and the third is just like Text19 that I described above, where you set the Control source to =1, the running sum property to over all, and the visible to No.

4. Lastly, I have a SumOfDeposits Footer. I have one textbox in that with the control source set to

=IIf([Text19]=1,[BalanceValue]+[SumOfDeposits]-[SumOfContributions],[SumOfDeposits]-[SumOfContributions])

with the Running Sum property set to Over All.

That give me a report that groups by month, give me a total Deposits for the Month, total Contributions for the Month, and a Footer balance that shows a running balance.

Give it a go and post back with specific problems.

Paul

 

[PaulBricker]

In re-reading the last post, I noticed my DateSerial functions are not consistent. I was playing around with different setups while I was posting. All the DateSerial functions should be the same in the SQL.

DateSerial(Year([Date]),Month([Date])+1,0) gives you the last day of the month.

DateSerial(Year([Date]),Month([Date]),1) gives you the first day of the month.

Sorry I should have proof read the post.

Paul

 

[PaulBricker]

In looking thru some old posts, I found some info that might help. You can create a query using this select statement

SELECT tblBalance.Date, tblBalance.Deposits, tblBalance.Contributions, (SELECT Sum([Deposits]) - Sum([Contributions]) FROM tblBalance As A WHERE A.[Date]<=tblBalance.[Date]) AS RunningBalance
FROM tblBalance
ORDER BY tblBalance.Date;

You may find that you can use the balances here easier than what I was proposing earlier.

Paul

 

[CoolFactor]

Paul,

Thank you for that last tip and pretty much all your tips. I was wondering can we use this last tip that you posted in way where we can get that running balance back one month. What I mean is this last tip gives me the right amount like I wanted but now I just need that amount to go back one month. So if I happen to type in the date range for October I would get the ending balance for October but I would also like to include the ending balance for September regardless of me typing in the date range from 10-01-06 to 10-31-06. Let me know if you understand.

 

[PaulBricker]

Where are you typing these parameters in. If you are opening the query, and putting in the parameters there, then I don't know a way off the top of my head. If you are entering the dates into a form they you could probably manipulate the data to include the ending balance from the previous month. Give me a little more info, and I'll see what I can come up with.

Paul

 

[CoolFactor]

I created a form in that looks like this:

Enter beginning date: text box

Enter ending date: text box

with two command buttons: OK & Cancel

This form is tied to a the report I've been telling you about:

I created the report using the report wizard and I grouped the report by Month.

When I enter the beginning and ending date it now produces the right amount that I asked you for initially in the date footer. So now in the Date Header is where I want to see that amount back one month and in the report footer I still want to continue to see the ending balance for that month, which is what you helped me on initially.

 

[PaulBricker]

You can get the value into a textbox on the form using an expression like this in the Click Event for your form.
Dim myDte as Date
myDte = DateSerial(Year([textboxBeginningDate],Month([textboxBeginningDate]),0)
=DMax("RunningSum","qryRunningSum", "[Date] <= #" & myDte & "#"

The DateSerial() function returns the last day of the previous month.

Once you get that value into you form, you can pass it to the report using the global variable we talked about earlier.

Paul

 

[CoolFactor]

Hey Paul,

In this last suggestion where you direct me to use the click event in the form, where exactly do you want me to input this code. How exactly do I pass this on to the report using the global variable.

Thank you,

CoolFactor

 

[PaulBricker]

First, in a regular module paste this code at the top of the module.

Public gdblRunSum as Double

Public Function RunSum()
RunSum = gdblRunSum
End Function

Then, in the click event for your OK button put this code It would look like this

Dim myDte As Date

      myDte = DateSerial(Year([textboxBeginningDateName],Month([textboxBeginningDateName]),0)

Me.textboxToHoldBalanceName = DMax("RunningBalanceFieldName", "qryRunningBalanceQueryName", "[DateFieldName]< #" & myDte & "#")

gdblRunSum = Me.textboxToHoldBalanceName
DoCmd.OpenReport "rptNameHere", acViewPreview, , "[DateFieldName]>= #" & [textboxBeginningDate] & "#"

Then put a textbox in your report Date header that has a control source set to

=RunSum()

Give it a go and let me know if you have any problems.

Paul

 

[CoolFactor]

Hey Paul,

I was wondering if you could help me out with this post. Except that the problem is much simpler than what we have discussed before. I have the following code in a text box and now how can I add where you dim back a Month?

=Sum(IIf([Contributions/Disbursements]>0,([Contributions/Disbursements]*-1),[Deposits/Receipts]))

 

[PaulBricker]

I have the following code in a text box and now how can I add where you dim back a Month?

I'm not sure what you are asking. Can you rephrase the question. Sorry if I'm missing the obvious.

Paul

 

[CoolFactor]

Do you remember this question that I ask you before:

Paul,

Thank you for that last tip and pretty much all your tips. I was wondering can we use this last tip that you posted in way where we can get that running balance back one month. What I mean is this last tip gives me the right amount like I wanted but now I just need that amount to go back one month. So if I happen to type in the date range for October I would get the ending balance for October but I would also like to include the ending balance for September regardless of me typing in the date range from 10-01-06 to 10-31-06. Let me know if you understand.

You then gave me something that looks like this(this is just a piece of what you wrote back):

Dim myDte As Date

myDte = DateSerial(Year([textboxBeginningDateName],Month([textboxBeginningDateName]),0)

Me.textboxToHoldBalanceName = DMax("RunningBalanceFieldName", "qryRunningBalanceQueryName", "[DateFieldName]< #" & myDte & "#")

gdblRunSum = Me.textboxToHoldBalanceName
DoCmd.OpenReport "rptNameHere", acViewPreview, , "[DateFieldName]>= #" & [textboxBeginningDate] & "#"


I all I want to do now is to get that running balance back one month in a text box.

 

[CoolFactor]

The text box is on the report in the Date Header section.
I have a running balance like so: =Sum(IIf([Contributions/Disbursements]>0,([Contributions/Disbursements]*-1),[Deposits/Receipts]))
in a text box and have also set the Running Sum Option to overall in the Data Tab. This gives me the right amount at the end of the month. For example lets just say that the ending balance for October is $30,000. What I want to know is instead of showing me the ending balance for October, I would like to see the ending balance for September whatever that may be.

This works just fine:
=Sum(IIf([Contributions/Disbursements]>0,([Contributions/Disbursements]*-1),[Deposits/Receipts]))
in a text box and have also set the Running Sum Option to overall in the Data Tab

Now I just want to add that element that will scale it back a month.

 

[PaulBricker]

You will have to play with the DateSerial expression and/or the DMax expression. Currently, the DateSerial expression returns the last day of the previous month. So if you type in 10/1/06, the expression returns a value for myDte as 9/30/06 and then the DMax expression finds the balance for the max date that is less than 9/30/06. If you want a balance for the beginning of that month, in this case September, you might have to change the two expression to something like this

myDte = DateSerial(Year([textboxBeginningDateName],Month([textboxBeginningDateName])[blue]-1,1[/blue])

Me.textboxToHoldBalanceName = DMax("RunningBalanceFieldName", "qryRunningBalanceQueryName", "[DateFieldName][blue]<=[/blue] #" & myDte & "#")

Try something like this and post back with problems.

Paul