none
creating a running total for a statement of Account RRS feed

  • Question

  • I am writing a code to create a statement of account to include Charges, Credit and daily balance. I am doing this in Vb.Net with access tables. I want to be able to keep a running total(Balance) the picture bellow is what it looks like. At the moment I am able to do the addition and save it to a table but the running balance does not add to the previous balance : balance= Credit – debit  and insert to the table.

    I know the problem is in the query and will like if I could get some help to improve my query shown below. Thanks for your time.

     Dim credit As Double
            Dim charges As Double
            Dim balance As Double
            credit = txtCredit.Text
            charges = txtCharges.Text
            balance = credit - charges
            conn.ConnectionString = connString
            conn.Open()
            cmd.Connection = conn
            cmd.CommandText = "INSERT INTO AcctStatementMaster(Datetbl, Code, Description, Charges, Credit,Balance)VALUES( @Dte, @PmtCode, @Des,@Charges, @Crdt,@Bal)"
            cmd.Parameters.AddWithValue("@Dte", txtDate.Text)
            cmd.Parameters.AddWithValue("@PmtCode", txtPmtcode.Text)
            cmd.Parameters.AddWithValue("@Des", txtDescription.Text)
            cmd.Parameters.AddWithValue("@Amt", txtCharges.Text)
            cmd.Parameters.AddWithValue("@Pmt", txtCredit.Text)
            cmd.Parameters.AddWithValue("@Bal", balance)
            cmd.ExecuteNonQuery()

    Thursday, July 13, 2017 1:27 PM

All replies

  • At the moment I am able to do the addition and save it to a table but the running balance does not add to the previous balance

    Where does the previous balance come from?  If you get that value, then the expression is

            balance = previous_balance + credit - charges

    so you need some code that finds the previous balance.

    You have implicit conversions from strings to numerics, and that will fail.  See:
    https://msdn.microsoft.com/en-us/library/zcd4xwzs(v=vs.80).aspx

    Thursday, July 13, 2017 11:33 PM
  •  I want to be able to keep a running total(Balance) the picture bellow is what it looks like. At the moment I am able to do the addition and save it to a table but the running balance does not add to the previous balance : balance= Credit – debit  and insert to the table.

    Hi alobi,

    What is running balance, what is previous balance, and where these come from, can you give one example here, it is beneficial to us to understanding your mean.

    Best Regards,

    Cherry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, July 14, 2017 8:12 AM
    Moderator