Results 1 to 12 of 12
  1. #1
    Join Date
    Aug 2019
    Location
    Chicagoland
    Posts
    3

    Trying to figure out iff I can make Access add and subtract numbers for bank ledger

    Hello Community,

    I am new to this forum, so please forgive me if I am not putting this question in the correct sub forum. I am also relatively new to MS Access (I am working with the 2016 version). I am trying to create a bank ledger in Access 2016 - I have already created on in Excel, but I am trying to get better with Access and I thought this would be a good way to practice.

    The problem that I am running into is that I have two fields - Credit and Debit - and when I enter a number into one of the fields I would like Access to either add or subtract that number and give me a total (See screenshots.) Is there a VBA code that I have to program into Access or is there a simpler way?
    Attached Thumbnails Attached Thumbnails Frm.PNC_Checking_Small.PNG   Tbl.PNC_Checking_small.PNG  

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    You can do it any number of ways. On your form a textbox of:

    =Debit - Credit

    In the table, you could try a calculated field with the same calculation.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Should have added this:

    http://allenbrowne.com/casu-14.html
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  4. #4
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    I would like Access to either add or subtract that number and give me a total
    not sure how your form is intended to work (add or subtract from what?), but a transaction would either be a debit or a credit, not both. Consequently one of them will be null, so you need to use the nz function

    nz(debit,0)-nz(credit,0)

    however it should not be necessary to store the total, you can determine it in a query or form whenever you needed it.

    if you mean what is typically called a 'running sum' or 'balance to date', then even more reason not to store the value, but calculate it when required because the balance can change if a transaction is entered with an earlier date than the existing transaction. Won't go into how you do that now because your requirement isn't clear.

  5. #5
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Just enter the raw data then build a report to do balance calc.

    An alternative structure is one field for Amount and another field to select "Debit" or "Credit" (or maybe "Deposit" or "Withdrawal"). Not seeing field for transaction document identifier (check number or deposit number) - call the field TransRef. If transaction is a deposit, there would not be a 'company' (payee).

    Unless you are developing a double-entry accounting system, would avoid terms "Debit" and "Credit". In double-entry accounting, some account types have a normal positive Debit balance and some have normal positive Credit balance. In double-entry accounting Debit and Credit just mean "left" and "right" columns of ledger.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    In double-entry accounting Debit and Credit just mean "left" and "right" columns of ledger.
    One place I worked many years ago, a book keeper had put together a procedures manual - debits were 'the column nearest the window'. Played havoc with the person who covered for her on holiday - she sat opposite!

  7. #7
    Missinglinq's Avatar
    Missinglinq is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    May 2012
    Location
    Richmond (Virginia, not North Yorkshire!)
    Posts
    3,016
    This appears to be a ledger for checking accounts (Table name = PNC Checking) so wouldn't you have to use DSum() against credits and debits for a given account to get the total of each...and then subtract one from the other?

    Or am I missing something?

    Linq ;0)>
    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  8. #8
    Join Date
    Aug 2019
    Location
    Chicagoland
    Posts
    3
    Thanks for all of your help. I don't think that I explained it well enough. Missinglinq was correct; this is a bank ledger. I'm still super new to Access so aome of your suggestions were hard to implement. I got lucky and googled "Access add subtract for ledger" (or something like it) and came across this link: https://edu.gcfglobal.org/en/access2...totals-rows/1/. This was basically what I was trying to do (see the attachment,)

    There are still more things that I want to do like make the negative amounts in the "Amount/Total" fields red and the positives green. Is there an easy way to do that? In Excel I could just create a rule, but I don't know if Access works that way.Click image for larger version. 

Name:	PNC.PNG 
Views:	23 
Size:	55.9 KB 
ID:	39353

  9. #9
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Post 8 was moderated, I'm posting to trigger email notifications.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  10. #10
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Set textbox Format property. Review https://support.office.com/en-us/art...F-DAA0BB709620
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  11. #11
    Join Date
    Aug 2019
    Location
    Chicagoland
    Posts
    3
    Thanks June7. I tried what the article stated and wrote [red] in the format field in the property box, but nothing happened - I tried all of the colors too with the same result. Do you have any idea what I am doing incorrect? Also, instead of one color, I would like to get two colors for the text; green for a positive amount of currency and red for a negative amount (or debit.) Any ideas?

  12. #12
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Follow example in link. Format property for numbers has 4 parts separated by semi-colon. If you don't want to apply one of the parts, leave it blank. Can't use color alone:

    $#,##0.00[Green];($#,##0.00)[Red];0;
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Subtract 2 numbers based on date
    By dfoster in forum Tutorials
    Replies: 3
    Last Post: 04-16-2019, 03:49 PM
  2. Replies: 8
    Last Post: 02-22-2019, 10:36 AM
  3. How to make amounts subtract
    By simmiepam in forum Reports
    Replies: 24
    Last Post: 02-24-2018, 10:01 PM
  4. Replies: 9
    Last Post: 03-07-2017, 02:49 PM
  5. Figure Numbers incrementing over Page Breaks
    By jezzamax in forum Reports
    Replies: 3
    Last Post: 12-19-2010, 08:05 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums