I created this query with a running sum, which is sorted by date then by 
transaction number. I put this into a form for entering transactions for an 
investment money market account. It works very well. 

However, now that I have one years worth of data in there, I would like to 
limit the data for the form as well as for reports. As soon as I limit the 
data range, I loose the beginning balance. 
Can anybody give me an idea on how I could create a beginning balance when I 
select a date range or a starting date?

Anne wrote:
>I created this query with a running sum, which is sorted by date then by 
>transaction number. I put this into a form for entering transactions for an 
>investment money market account. It works very well. 
>
>However, now that I have one years worth of data in there, I would like to 
>limit the data for the form as well as for reports. As soon as I limit the 
>data range, I loose the beginning balance. 
>Can anybody give me an idea on how I could create a beginning balance when I 
>select a date range or a starting date?

Only way I can think of eliminating the problem is to store month end
balances.  Then you can look up the last balance you need and use it in your
calculations.  You would have to union that result with your current/filtered
data and then you should be able to get proper results.  Yes, I know what I
recommend contradicts the "never store calculated totals" rule, but in this
case, I think it's worth it.

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200912/1

While searching the internet for a solution, I found a similar post as mine 
with an answer from Marshal Barton, who suggested creating a temporary table. 

I already had a running sum total using DSum. In the query I create a field 
which could give me the previous balance, which is the current running 
balance less the transaction amount: PriorBal: [RunBal]-[TransAmount]

Upon opening the form, a make table query creates a temporary table with the 
running balance everytime the form is opened. This table is used for the 
reports. In the form header I show a text box with the "PriorBal" and it 
looks really good.



"PieterLinden via AccessMonster.com" wrote:

> Anne wrote:
> >I created this query with a running sum, which is sorted by date then by 
> >transaction number. I put this into a form for entering transactions for an 
> >investment money market account. It works very well. 
> >
> >However, now that I have one years worth of data in there, I would like to 
> >limit the data for the form as well as for reports. As soon as I limit the 
> >data range, I loose the beginning balance. 
> >Can anybody give me an idea on how I could create a beginning balance when I 
> >select a date range or a starting date?
> 
> Only way I can think of eliminating the problem is to store month end
> balances.  Then you can look up the last balance you need and use it in your
> calculations.  You would have to union that result with your current/filtered
> data and then you should be able to get proper results.  Yes, I know what I
> recommend contradicts the "never store calculated totals" rule, but in this
> case, I think it's worth it.
> 
> -- 
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200912/1
> 
> .
>