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