Workaround for calculated control from report to new report

[tbjmobile@gmail.com]

I have a report which is a monthly account. Does anyone have a trick to get the closing balance, which is obviously calculated so can't be used in an update query, onto the next month's account as a balance brought forward please?

[CJ_London]

you don't store calculated values so an update query should be irrelevant

you could use a union query to union a group by query to sum all transactions up to the opening balance to a query that lists this months transactions. Include in your groupby query the opening balance date as a 'transaction date' so when sorted it will appear at the top

[pbaldy]

You can also use a DSum() on the report to sum all transactions prior to the start date of the report.

[tbjmobile@gmail.com]

Hello pbaldy,
I've not used DSum yet. Do you mean place a DSum on the new account to sum the calculated controls on the old account? At the moment I'm making a copy of the current account, renaming it as the new one and running a query to set all the calculated controls to 0 or "" if they are strings. If I'm correct about the above, are you suggesting that I then make 'OpeningBalance' on the new account equal to the DSum?
Thanks.

[pbaldy]

You mentioned a report. I'd have a DSum() in the header that got the beginning balance, add/subtract the amounts in the detail, and have a control in the footer that calculated the new ending balance. I'm not sure what you mean by making a copy of the current account. Normally you'd control what account was displayed with criteria.

[tbjmobile@gmail.com]

Hello again pbaldy,
What I meant was that in a previous database (Alpha 5) at the end of the month, I would first make a copy Account table once it had completed and had been sent. I would do as you suggest, sum all the in's and out's to get a new starting figure from the new (copied and renamed) account. What I was wondering is, could I have a text box on the new account to DSum those figures from the old one? In actual fact I would only need to sum 'StartingBalance' - 'MonthTotal' I think.

[Minty]

You shouldn't be saving new tables per month, just filter your information and sums based on transaction dates and transaction types.