Debit  - Credit Calculation

Posted on 2003-04-10
Last Modified: 2010-08-05
I'm developing Accounts & Receivable Module. I find difficult when I calcuate the debit credit calculation, for e.g.

My report figure should be

Date          Particular     Debit     Credit     Balance
1/2/2002     Inv 292             $1200          $1200
2/2/2002     Paymnt 219          $ 500     $ 700
5/2/2002     Inv 392          $200          $ 900
6/2/2002     Inv 394          $ 50          $ 950

In above statement, how can I calculate the Balance in Access.

Before this type of question posted also in
Question by:asjahir
    LVL 1

    Accepted Solution

    You almost had it with that link you provided.

    All you need to do is put a text field in the detail section under balance. In the control source for that text box put:
    Now the part you were missing:
    9 boxes below the Control Source in the properties window you should see Running Sum. Change that to Over All and that should do it.
    LVL 33

    Expert Comment

    by:Mike Eghtebas
    Another option is to add a field to your RecordSource query.  Call this field Balance where:

    Balance: [Debt]-[Credit]

    and enter Balance for ControlSource property of txtBalance on your form.

    If you want to have displays like ($120.00) for -$120.00, you need to revise Format property of txtBalance on your form to do this.


    LVL 8

    Expert Comment


    Do you want the balance result like this:

    Date       Particular Debit     Credit      Balance
    1/2/2002 INV 292     $1,200.00     $1,200.0 $0.00
    2/2/2002 Paymnt 219  $500.00     $700.00      ($200.00)
    2/5/2002 Inv 392     $200.00     $900.00      ($900.00)
    2/6/2002 Inv 394     $50.00     $950.00      ($1,800.00)

    LVL 28

    Expert Comment

    No comment has been added lately (70 days), so it's time to clean up this TA.
    I will leave a recommendation in the Cleanup topic area for this question:

    RECOMMENDATION: Award points to rrein http:#8311278

    Please leave any comments here within 7 days.



    EE Cleanup Volunteer

    Expert Comment

    Need help please!!!!!!
    i would like to build a database using access, that gives a running total every morning from the previous day debits and credits. however i would like to get the information from excel every morning. Note: i have over a 40,000 accounts, however not all 40,000 account have a transaction everyday.
    my report should look  like this each morning:
    My report figure should be

    Account#       Date         Balance      Debit     Credit     Balance
    154542121   1/2/2002     $400,000   $1200                  $398,800.00
    1545454       2/2/2002     300,000                $ 700       $300,700.00
    5454545       5/2/2002     1500.00    $200       $ 900     $2,200.00
    54545454     6/2/2002     2000.00         0               0           noTransaction
    LVL 1

    Expert Comment

    You should post your question in a seperate topic. But why not just use Excel? If there are some things you need Access for, just link to the excel sheet from Access.
    LVL 33

    Expert Comment

    by:Mike Eghtebas
    Hi SpideyMod,

    How can I search for a member name (AliciaV or AlicaV).  I have the excact very simmilar solution (about sep 2003) in one of her question.  I don't have time (now) to reproduce it.

    Any idea?  I couldn't find her name by sorting Answered Questions list.  


    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
    QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
    Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
    With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

    729 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now