Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Debit  - Credit Calculation

Posted on 2003-04-10
8
Medium Priority
?
3,544 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
Comment
Question by:asjahir
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 1

Accepted Solution

by:
rrein earned 80 total points
ID: 8311278
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 34

Expert Comment

by:Mike Eghtebas
ID: 8311349
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
ID: 8311445
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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 28

Expert Comment

by:TextReport
ID: 8769382
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
ID: 11633970
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
ID: 11636004
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 34

Expert Comment

by:Mike Eghtebas
ID: 11638641
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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

610 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