Forum Discussion

Tony2021's avatar
Tony2021
Steel Contributor
Dec 16, 2021
Solved

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]                                   ^^...
access
  • JurgenGeelen's avatar
    JurgenGeelen
    Jan 16, 2022
    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
JurgenGeelen's avatar
JurgenGeelen
Brass Contributor
Jan 15, 2022
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.
  • Tony2021's avatar
    Tony2021
    Steel Contributor
    Jan 15, 2022

    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.

    • JurgenGeelen's avatar
      JurgenGeelen
      Brass Contributor
      Jan 16, 2022

      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

       

      • Tony2021's avatar
        Tony2021
        Steel Contributor
        Jan 16, 2022

        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")