Dec 16 2021 09:08 AM - edited Dec 16 2021 09:10 AM
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!
Jan 15 2022 12:20 PM
Jan 15 2022 04:26 PM - edited Jan 15 2022 04:26 PM
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.
Jan 16 2022 01:31 AM
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
Jan 16 2022 12:31 PM - edited Jan 16 2022 01:05 PM
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")
Jan 16 2022 12:59 PM
SolutionJan 16 2022 05:03 PM
Jan 16 2022 12:59 PM
Solution