Craete query to filter customers when it is the time of their monthly patments

[AlefAelol]

Hi everybody, I'm not very to Access.
I'm trying to make a query to filter or show only those customers when it is the time for their monthly payment .
The query I have consists of four fields which are
1- Order ID
2- Payment
3- Date (Default value set to Date ())
4- Date for next time Payment (Default value set to date () + 30)

I made another field called "states". In this field I putted the following expression

IIf(([Date for next time Payment]-Date())= 0 "Should Pay";" ")

Then, I set the criteria for such field to "should pay" so that only customer "should pay" will appear in the query datasheet.
However, this method has the following shortcomings:
1- It works only for one day (alert day) ,i.e., customers will filtered only when the expression is true.
2- Customers who have paid will still appear as a "should pay" until the day (the day that make the expression true) finish.
3- Customers whose pay late (maybe after 1 week) will disappear form the query datasheet after alert day finish.

To overcome the above shortcomings, i modified the expression to

IIf(([Date for next time Payment]-Date())<-1 And ([Date for next time Payment]-Date())>-15;"Should Pay";" ")

This method will extend the alert duration to 15 days, so the customers who haven't pay yet will appear in the query datasheet for 15 days.
However, the customers who have payed will appear also, and that is the problem.
The problem here is I can't compare the current payment date with previous one for one customer.
is there any suggestions or method to create suitable expression, or even another query scheme ??

 

[burrina]

I would ask, what do you do when the month has over 30 days? How are you handling balances remaining? Does Date get adjusted when they make a payment? FYI, Date is a Reserved word and should be named something else, like PymntDte.