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.

 

[CoolFactor]

Paul,

Have an idea and wanted run pass you to see if this works a lot easier. Instead of allowing them to input a date range like we've been discussing thus far; how about if I change this so that they could only select a year and then they could select a range between months. So on my form it would have where they could select the year and a month or they could select a year and then select for example January to March.

Would this help me out with the expression I want to build in the textbox on my report.

In addition, what would the expression look like with out the report being parameterized.

Let me know if you need any clarification.

Thank you for all help.

 

[PaulBricker]

Before we go and change everything, lets look at what we have.
Currently, the way the code is written, when someone enters a date, we look for a [blue]beginning[/blue] balance that is from the previous month. What you are looking for is the [green]running[/green] balance from the preceeding month. So if someone inputs a date of 10/1/06, you want the running balance in the report to start with 9/1/06. Is that correct? But what happens if someone enters a date of 10/20/06? Do we want the running balance to start with 9/20/06 or back to 9/1/06?
You also ask what the expression would look like without the report parameterized. Which expression are we talking about. The open report expression? If that's the case, then you would just remove the last argument from the expression.

Paul

 

[CoolFactor]

If someone enters the 10/1/06, I want them to see the running balance in the date header within the textbox to start with 9/1/06. I actually want to make this a lot easier by having them on form select year and then a month or an option to enter a beginning month like March and then an ending month like April. Would this be easier than what we were trying to accomplish before?

The expression that I am referring to is the one that will go in the control source of the textbox. This is the expression that I am referring to when I say without the report being parameterized:
=Sum(IIf([Contributions/Disbursements]>0,([Contributions/Disbursements]*-1),[Deposits/Receipts]))

This gives me the right amount for the current month but not the previous month.

 

[PaulBricker]

Sorry, it has been a very busy couple weeks for me. I don't have a real opinion on whether it would be easier for your users to enter a date in a textbox or select some predefined values from a drop down box. It would probably be easier to control the values returned if we controlled what the user could input for dates. That would give some weight to using the dropdown boxes.
As for the expression you are talking about, I don't see that expression in any of my sample data. I don't think I have a good grasp on what that expression is doing for you.

Paul

 

[CoolFactor]

I actually do want to control what the users input for the dates. Instead of them inputting dates though, I just want them to be able to from a list box or combo box to select a year and then select the month of January to March literally or just January if their hearts desire it so.

As for the expression, you use it with following code:

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

CODE
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

All I did was in text box in the control source I inputted the following:

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

and I have also set the Running Sum Option to overall in the Data Tab

What this is doing is subtracting the contributions from the deposits and with the running sum option set to overall in the Data Tab; it then gives me the right total for the current month. I want to see what we could do with this expression so that we could get the previous month total.

Also the text box is in the date header and it has to stay there.

Thanks again

 

[PaulBricker]

If you are creating your running sum in the report, then you should be able to set the starting balance in the date header by subtracting 1 month from the user input and using a statement like

DMax("FieldName","TableName","DateField"<= #" & DateSerial(Year(DateTextbox),Month(DateTextbox)-1,1)& "#")

Then pass the value returned by the DMax() function to the global variable and plug it into your report.

I'm not entirely clear exactly where you are having problems so if this isn't what you are looking for, let me know and we'll try again.

Paul