Link to home
Start Free TrialLog in
Avatar of kgittinger
kgittingerFlag for United States of America

asked on

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...
https://www.experts-exchange.com/questions/22619649/Ageing-Report-Problems.html 


MyAgingTest-042809.mdb
ASKER CERTIFIED SOLUTION
Avatar of thenelson
thenelson

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of kgittinger

ASKER

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.

Best!
Avatar of thenelson
thenelson

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

Happy Computing!

Nelson

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.