Results 1 to 6 of 6
  1. #1
    Gul is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2022
    Posts
    3

    How to create receivable aging either in query or through VBA

    Hi everyone,
    Its my first question here in this forum.
    Following is a simple LEDGER:

    Click image for larger version. 

Name:	ledger.jpg 
Views:	29 
Size:	54.1 KB 
ID:	48002

    for the balance receivable amount, how can I create an aging report like below through query or VBA?



    Click image for larger version. 

Name:	aging.jpg 
Views:	29 
Size:	36.3 KB 
ID:	48003

    Any help will be appreciated.
    Thanks in advance.

  2. #2
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    Use the DateDiff() function?
    Not sure about your currency figures though?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  3. #3
    Gul is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2022
    Posts
    3
    May be I haven't well explained my question.
    In a situation where full payment is not received against invoice amount, instead, partial payments are received.
    I would like to have unpaid invoices according to the balance receivable.

    Click image for larger version. 

Name:	unpaid.jpg 
Views:	31 
Size:	64.1 KB 
ID:	48004

  4. #4
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    problem is you have a simple ledger and now want to do something complicated.

    your payments need to be assigned to an invoice at the time of receipt - so you need a column for that.

    If it is not assigned it remains unassigned and is reported separately.

    On what basis would you say that a payment of 3,540 is received on 20/02/2022 instead of the 10,000? Would that not be assigned to the invoice of the same amount dated 15/02/2022?

    And how do you handle invoices in dispute? or credits?

  5. #5
    Gul is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2022
    Posts
    3
    Quote Originally Posted by Ajax View Post
    problem is you have a simple ledger and now want to do something complicated.

    your payments need to be assigned to an invoice at the time of receipt - so you need a column for that.

    If it is not assigned it remains unassigned and is reported separately.

    On what basis would you say that a payment of 3,540 is received on 20/02/2022 instead of the 10,000? Would that not be assigned to the invoice of the same amount dated 15/02/2022?

    And how do you handle invoices in dispute? or credits?
    Yes, you are right. I am doing something complicated. I think it is possible but don't know how. I'm novice.
    I want the list of unpaid invoices (based on the balance receivable) without assigning payments to them as shown in this image: https://www.accessforums.net/attachm...chmentid=48004
    Getting unpaid invoices after assigning payments to them is a simple task. I can do that. I want something complicated.

  6. #6
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    my point is your example is incomplete unless you are saying you have already removed the assigned invoices - which given the PK's you have seems unlikely.

    Don't think I can help. Your data is incomplete, you have not answered my questions and not provided a realistic example to cover all eventualities.

    The only thing I can suggest which might work is to use a cartesian query (no joins) or perhaps one with non standard joins.

    1. You will need a way to sum invoice value to date for each invoice - much the same as your balance calculation but excluding payments.
    2. Then sum total payments
    3. Then find the max date where invoice value to date < total payments - these will be 'paid in full'. Note the <, do not use <= makes the next step more complicated
    4. Then find the next record which will have a partial (or full) payment (diff between invoice value to date and total payments)
    5. All the rest will be unpaid.

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

Similar Threads

  1. How to get latest Receivable Amount?
    By Athar Khan in forum Access
    Replies: 6
    Last Post: 09-23-2017, 11:22 AM
  2. 30/60/90 aging query on amoritzation table
    By Gina Maylone in forum Queries
    Replies: 2
    Last Post: 07-30-2016, 08:56 AM
  3. Stock aging code
    By UncleKay in forum Access
    Replies: 29
    Last Post: 06-05-2016, 07:31 PM
  4. Aging Report
    By Jblackbelt in forum Reports
    Replies: 10
    Last Post: 10-08-2014, 08:02 PM
  5. Aging A/P function
    By nim73 in forum Programming
    Replies: 0
    Last Post: 05-07-2009, 01:23 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