Running Total (1 Viewer)

CJ_London

Super Moderator
Staff member
Local time
Today, 13:30
Joined
Feb 19, 2013
Messages
16,553
it's a method I use all the time particularly for financial reporting.
 

MikeT1941

Member
Local time
Today, 13:30
Joined
Nov 18, 2020
Messages
46
OK- here we go.
I made slight alterations to the code for keytest to produce the following key 006951838102000025, being one of the first ones.
The key wording is................... keytest: (Format([HistoryID],"00000") & Format([InvoiceDate],"yyyymmdd") & Format([ID],"000000"))
This gives me the data sorted by Job, then Date, then ID in case there are two or more entries with the same date.
I've left it as text because it went to an exponential if turned into a number.
So now to the TrialBal
I've tried various permutations of the criteria wording, but it wont play so I've left it at what you wrote. I still don't understand the syntax underpinning it, though.

BalTrial: CCur(DSum("[CalcTodaysValue]","BillsIssuedAndPaidQuery","keytest<='" & keytest & "'"))
Thanks
Mike
 

CJ_London

Super Moderator
Staff member
Local time
Today, 13:30
Joined
Feb 19, 2013
Messages
16,553
So just to confirm - it is now working as required?

Note that adding the history ID into the key doesn't actually add anything to the solution and may actually slow the whole process down as a larger string needs to be processed which takes more time. Also HistoryID will be (or should be) indexed so by including it in your key, you lose the benefit of indexing. May not matter if you only have a few records, but once you get into the thousands then likely to become apparent.

I still don't understand the syntax underpinning it, though.
don't understand what this means - what syntax?
 

MikeT1941

Member
Local time
Today, 13:30
Joined
Nov 18, 2020
Messages
46
My apologies for not being clear. No, it doesn't work. As you suggested, I've removed the HistoryID from the key and transformed it to a number without difficulty this time. Query is sorted by HistoryID and then by Keytest
Forget about the syntax- I think I have it now.

The new BalTrial wording is BalTrial: CSng(CCur(DSum("[CalcTodaysValue]","BillsIssuedAndPaidQuery","keytest<=" & [keytest]))) but this gives rise to an error although Keytest is fine
Mike
 

CJ_London

Super Moderator
Staff member
Local time
Today, 13:30
Joined
Feb 19, 2013
Messages
16,553
BalTrial: CSng(CCur(DSum("[CalcTodaysValue]","BillsIssuedAndPaidQuery","keytest<=" & [keytest])))

having converted to currency, why are you then converting to a single?

If you have now removed historyID from the keytest calculation, you need to include it in your dsum criteria. I would have expected

BalTrial: CCur(DSum("[CalcTodaysValue]","BillsIssuedAndPaidQuery","keytest<=" & [keytest] " & " AND HistoryID=" & HistoryID))


 

MikeT1941

Member
Local time
Today, 13:30
Joined
Nov 18, 2020
Messages
46
Single conversion removed- call it brain fade!

I tried the new version of BalTrial without CSng but regret that it "was not correct syntax"
 

MikeT1941

Member
Local time
Today, 13:30
Joined
Nov 18, 2020
Messages
46
Well guys you did it.

BalTrial: CCur(DSum("CalcTodaysValue","BillsIssuedAndPaidQuery","[keytest]<=" & [keytest] & " And [HistoryID]=" & [HistoryID]))

The difference appears to be that the formation of the keytest index, which was the invoice date plus the value of the index of the subform, had to be moved to the first query, not kept in the subquery.

Anyway it does work:

I'm rewriting this bit as it disappeared.

The sub-total at item 120 equals the difference you can see on the bottom of the visible ledger page and the final balance is zero- as required.

The bit about outstanding balance can now extend to days outstanding and interest notionally due. That's for tomorrow.

So, many thanks to Mike Smart who did most of the heavy lifting and CJ who polished the result.

I'm sure it won't be my last question, but I do appreciate the time you guys put into it.

Mike
 

Attachments

  • 1648490449077.png
    1648490449077.png
    1.1 MB · Views: 69
Last edited:

Users who are viewing this thread

Top Bottom