Forum Discussion
Opening Balance query (grab record above)
Experts, I have a query that has a [AmountOfChange] and [closing Balance]
[AmountOfChange] = manual entry
[Closing Balance] = [OpeningBalance]+[AmountOfChange]
^^^^^this is the issue
What I now need is an Opening Balance which would be the [Closing Balance] (calculated field shown above) but for the first record the Opening Balance would need to be overridden with a 0 then going forward the [Closing Balance] calculation would be for the record above [tblAmendHistory].[ID] on me.[tblAmendHistory].letterofcreditID (its something like that). So basically, the [Closing Balance] from record above is now the "Opening Balance" for the new record. Its a little difficult to explain. It might make more sense to see the attached excel.
Possibly would need a helper field? I dont know but thinking an expert has encountered this before.
I am open to any solution.
Please see attached accb and excel.
The accdb is not correct in that the opening balance calculated field is wrong. The excel file shows what I am looking for.
thank you for the help!
- Here is the complete query:
SELECT tblLCAmendHistory.*, Format(Nz(DSum("AmountOfChange","tblLCAmendHistory","letterOfCreditID=" & [tblLCAmendHistory].[letterOfCreditID] & " And [ID] < " & Nz([tblLCAmendHistory].[ID],0)),0),"Currency") AS [Opening Balance], [Opening Balance]+[AmountOfChange] AS [Closing Balance]
FROM tblLCAmendHistory
6 Replies
- You can try this in your query:
Opening Balance: Format(Nz(DSum("AmountOfChange";"tblLCAmendHistory";"letterOfCreditID=" & [tblLCAmendHistory].[letterOfCreditID] & " And [ID] < " & Nz([tblLCAmendHistory].[ID];0));0);"Valuta")
I prefer not to use functions like dsum in large datasets. The performance is bad.
I prefer to add a field 'Opening balance' in your table. For very record you add you can fill the field with your dsum function.Hi Jurgen, thank you very much for the response.
I seem to have a syntax though it seems.
Could you kindly assist? Thank you. Also, what is "Valuta"? I have never head of it.If you open the query in Access you see this field:
In this query field you see this :
Opening Balance: Format(DSum("AmountOfChange";"tblLCAmendHistory";"letterOfCreditID=" & [tblLCAmendHistory].[letterOfCreditID] & " And [ID] <= " & [tblLCAmendHistory].[ID]);"Valuta")
replace it with:
Opening Balance: Format(Nz(DSum("AmountOfChange";"tblLCAmendHistory";"letterOfCreditID=" & [tblLCAmendHistory].[letterOfCreditID] & " And [ID] < " & Nz([tblLCAmendHistory].[ID];0));0);"Valuta")
The text "Valuta" is a format function: format([value],"valuta")
It shows the valuta sign. The function was already in the field
