Solved

Debit  - Credit Calculation

Posted on 2003-04-10
3,462 Views
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
 http://www.experts-exchange.com/Databases/MS_Access/Q_20338642.html
0
Question by:asjahir
    7 Comments
     
    LVL 1

    Accepted Solution

    by:
    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:
    =[DEBIT]-[CREDIT]
    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.
    0
     
    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.

    Regards,

    Mike
    0
     
    LVL 8

    Expert Comment

    by:Benjamin_Luk
    Asjahir,

    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)

    Ben
    0
     
    LVL 28

    Expert Comment

    by:TextReport
    asjahir,
    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.

    PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!

    Thanks,

    TextReport
    EE Cleanup Volunteer
    ---------------------
    0
     

    Expert Comment

    by:gold64111
    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
    0
     
    LVL 1

    Expert Comment

    by:rrein
    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.
    0
     
    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.  

    Mike
    0

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone. Privacy Policy Terms of Use

    Featured Post

    IT Security CISA, CISSP & CISM Certification

    Master the advanced techniques required to protect network resources from external threats with the IT Cyber Security bundle. Built around industry best-practice guidelines, the IT Cyber Security bundle consists of three in-depth courses.

    Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
    In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
    Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
    In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

    875 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

    12 Experts available now in Live!

    Get 1:1 Help Now