Solved

Debit  - Credit Calculation

Posted on 2003-04-10
3,459 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

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
    A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
    As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
    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…

    690 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

    25 Experts available now in Live!

    Get 1:1 Help Now