How to apply payments against the oldest balance of items in an Access 2003 AR Aging

Posted on 2009-04-28
Last Modified: 2012-06-27
How to apply payments against oldest balance in an AR Aging query?  I have the attached dataset for which I have a query to move items into the appropriate aging buckets (Current, 31 tp 60 days, 61 to 90 days, 91 to 120 days and over 120 days) that you folks did a great job in setting up for me.  I cannot seem to aportion the buckets appropriately when payments are applied to items that are in a different bucket ( i.e. as in the example of acct 37504I - if a payment made in the 61 to 90 day period, how does this get applied against a charge in the 91 to 120 day bucket?)

I have tried iif balance - net activity of the 61 to 90 day bucket > 0, Balance -  (net activity of over 120) - (net activity of  90-120) - net activity of 61 to 90 days), 0.
BUT I just cannot get this to work so there has to be a FLAW in my logic and I accomplish this feat.

Your assistance is greatly appreciated.

PS - I have found a similar question posed but the zone was C Programing and there is no way I can apply to my situation...

Question by:kgittinger
    LVL 39

    Accepted Solution

    I think you are wanting to post a payment to the oldest charge to the account. If this is correct, You need to add a balance field to your TblTrans table. Initially the balance field would be set equal to the net_amount field. Then you would apply the payment to the oldest charge with a non zero balance and subtract the payment from the balance. If the payment is larger than the charge balance, then set the charge balance to zero and loop back to finding the oldest charge with a non zero balance.

    A psuedo code example:

    Dim intTblTransID As Integer
    Dim dtmOldestDate As Date
    Dim curBalance As currency
    Dim curPayment As Currency

    curPayment = Payment


       'Find the oldest charge with a non zero balance:
       dtmOldestDate = DMin ("event_date", "TblTrans", "Balance > 0")
       intTblTransID = DLookup ("ID", "TblTrans", "Balance > 0 AND event_date = " & dtmOldestDate)

       'Apply the payment:
       curBalance = DLookup ("Balance", "TblTrans", "ID = " & intTblTransID)
       If curPayment > curBalance Then
          db.Execute "Update TblTrans SET Balance = 0 WHERE ID = " & intTblTransID
          curPayment = curPayment - curBalance
          Goto PaymentNotZero
          db.Execute "Update TblTrans SET Balance = " & curBalance - curPayment & "WHERE ID = " & intTblTransID
       End If

    Note: I used Goto instead of Where out of laziness (and it is pseudo code).

    BTW a few comments:

    Referring to common accounting procedures:
    Since this looks like charge accounts which are debit accounts, purchases, finance charges and anything that increases the account balance should be posted as positive in the net_amount field (as you have it). Payments and anything that decreases the account balance should be posted as negative. Now simply summing that field will produce the account balance.

    Get rid of the Balance field in the Account table. It could get out of sync with the correct amount and can be easily calculated. A general database design rule is do not store anything that can be calculated.

    I would recommend not using the underscore in naming objects. Use capitals instead to make the names easy to read. For example change net_amount to NetAmount.

    It is best to avoid spaces and other special characters in your naming of objects. An extreme example of the problems you will have using special characters in your names:
    I named a textbox
    !@#$%^&*()_+= -{}:;"'<,>?/|~
    and had Access create an event procedure.  Access converted the name of the textbox to:
    so if the form had the name:
    ~!@#$%^&*()_+= -{}:;"'<,>?/|~
    referencing the textbox would be:
    A useful tip for someone who doesn't want someone else (probably even themselves) from reading their code.
    And spaces will sometimes cause problems in VBA references even after years of trouble free operation.


    Author Closing Comment

    I'll try to implement your recommendations.  I have not used Dim functions so if I run into trouble I will post a follow up question.  Thank you for the critique, I will follow up on your suggestions for improvments.

    LVL 39

    Expert Comment

    You're welcome.  Glad to help and thank you very much for the points with "A" grade!

    Happy Computing!


    The Dim statement dimensions (allocates the appropriate amount of memory) for a variable .  It is required before using a variable if you have an
    Option Explicit
    statement at the top of the module. Without the Option Explicit statement, VB will automatically dimension an unknown variable as a variant (which uses a lot of memory).  I suggest you use Option Explicit on all your modules. Without it VB will assume a simple typo is a new variable and you may spend hours trying to find the problem. With it VB will immediately popup "variable not defined" after you enter the typo.

    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

    Join & Write a Comment

    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…
    Creating and Managing Databases with phpMyAdmin in cPanel.
    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…
    With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

    733 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

    22 Experts available now in Live!

    Get 1:1 Help Now