Opening Balance query (grab record above)

[Tony2021]

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!  

[#2]

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.

[#3]

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.

[#4]

@Tony2021 

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

[#5]

@JurgenGeelen 

Hi Jurgen,
I think there is a difference in the comma and semicolon. This is why I have the syntax. Note I have replaced the semicolon with a comma in your formula above. I no longer have syntax. Also, I think I needed to replace "Valuta" with the word "Currency" to get the format correct.  It seems likely the differences are since we are in different countries. 

After making those edits, I think its very close but it needs to be tweaked a bit more and I have tried to modify to no avail.  Its far outside my knowledge of queries like this.  In the pic below, you can see what I am referring to.  I need the Closing Balance to be the Opening Balance in the row below it.  I hope it makes sense.  Thank you very much for the help, Jurgen.

Opening Bal Jurgen: Format(Nz(DSum("AmountofChange","tblLCAmendHistory","letterOfCreditID=" & [tblLCAmendHistory].[letterOfCreditID] & " And [ID] < " & Nz([tblLCAmendHistory].[ID],0)),0),"Currency")

[#6 · Accepted solution]

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

[#7]

that works. Amazing. You did that so easily. wow. thank you so much!