• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 777
  • Last Modified:

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

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...

  • 2
1 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.

kgittingerAuthor Commented:
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.

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

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now