Craete query for monthly patments

[falahsalih]

Hi everybody,
I'm trying to make a query to filter or show only those customer 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 ??

[ranman256]

Do you have a [paid] flag (some indicator for that month/client ) so when the customer pays, it wont show anymore?

[falahsalih]

Do you have a [paid] flag (some indicator for that month/client ) so when the customer pays, it wont show anymore?

ranman256, Thank you for the response
No, I have not, Can you help me how to make it ?
I appreciate it

[hapm]

Would be interesting to know how the "done payments" are saved: in the same record? in a different table? is there a reference between the "done payment" and the open payment

[falahsalih]

Would be interesting to know how the "done payments" are saved: in the same record? in a different table? is there a reference between the "done payment" and the open payment

I have only the done payment and it is entered manually by the user. The done payments is stored in the same query which its control source is a table.

[hapm]

So your query has a field that holds the date, when the payment should be done, and one for when the payment has been done? Than you simply need to check if the field for the date when the payment has been done is filled or not to filter out all done payments. You can use IS NULL for that.

[falahsalih]

So your query has a field that holds the date, when the payment should be done, and one for when the payment has been done? Than you simply need to check if the field for the date when the payment has been done is filled or not to filter out all done payments. You can use IS NULL for that.

I hope it is as simple as you mentioned. However, it is not. the query data appears only when a payment (done payment ) happened. i.e., the null fields are not shown in the query datasheet. The payment is entered by the person in charge manually.Moreover, the payments are not generated in advance using PMT function or some other methods. it Just like when a customer come to make payment (which is a random quantity of money ), then only this payment is stored in the query.

[hapm]

So it is saved in a different table? Then there should be a reference between the two tables. If not you wouldn't know which payment is for which "pay request".

[falahsalih]

So it is saved in a different table? Then there should be a reference between the two tables. If not you wouldn't know which payment is for which "pay request".

What you mean by the two tables ? I have only a table called "payments" and it is filled by a data entry form . the date of the query is based on that table. Please explain some more

[hapm]

I'm trying to guess your table structure. Think I guest wrong . Next try: you have a table, and this table has at least the two fields "planned payment day" and "done payment day"? If not, please explain how these are stored (in sence of table defintion), else you should be able to do, what I suggested yesterday.

[falahsalih]

I'm trying to guess your table structure. Think I guest wrong . Next try: you have a table, and this table has at least the two fields "planned payment day" and "done payment day"? If not, please explain how these are stored (in sence of table defintion), else you should be able to do, what I suggested yesterday.

Ok, I explain for you.
I have a main form which consists of many subforms. One of these subfroms is for the monthly payment. The control source of that subform is a table called "payments". Since a payments happened, the person in charge will fill that subform by some data which are
1- Payments (done payment or pay a payment). Here is the quantity of the many that the customer will pay today.
2- Date which refers to the current date by set the default value to "Date ()"
3- The next date payment. This field will filled by the current day + 30 , so it will assign the date of the next payment.

that is all what I have.
Regarding how the data are stored. Of course when the person in charge fills the subform, the table "payments" will be updated. i.e., the data will store here in the "payments" table. till here, I have no problem. I tried to make a query to filter those customer when it is the time for their monthly payment . I created a query based on the "payments" table. i.e., the control source or the reference for that query is the "payments" table. In the query, I added some fields and expressions as I have mentioned in the original post (please refer to the original post and read the problems I had ).

[hapm]

Now I got it, so your record contains two dates, but not for the same payment, but for the last and the next outstandig one. Easy in filling this up, but hard to relate the actual payment record to the corresponding record that is used as a reminder. I would have done it the other way round, so you have a new record in the payments table when the payment is planned, and with a null value in the "payment done" date field. Then it would be as easy as selecting all records that are null in that field, but it would mean that when the field gets a value, there must be a new record created for the new "planned" payment. I'm not sure if you have the ability to change your database scheme to that. If not, you will need a nasty join of your payments table with itself to get the "done payments"

[falahsalih]

Now I got it, so your record contains two dates, but not for the same payment, but for the last and the next outstandig one. Easy in filling this up, but hard to relate the actual payment record to the corresponding record that is used as a reminder. I would have done it the other way round, so you have a new record in the payments table when the payment is planned, and with a null value in the "payment done" date field. Then it would be as easy as selecting all records that are null in that field, but it would mean that when the field gets a value, there must be a new record created for the new "planned" payment. I'm not sure if you have the ability to change your database scheme to that. If not, you will need a nasty join of your payments table with itself to get the "done payments"

Well, how to make a new record for a "planned" payment ??

[falahsalih]

Now I got it, so your record contains two dates, but not for the same payment, but for the last and the next outstandig one. Easy in filling this up, but hard to relate the actual payment record to the corresponding record that is used as a reminder. I would have done it the other way round, so you have a new record in the payments table when the payment is planned, and with a null value in the "payment done" date field. Then it would be as easy as selecting all records that are null in that field, but it would mean that when the field gets a value, there must be a new record created for the new "planned" payment. I'm not sure if you have the ability to change your database scheme to that. If not, you will need a nasty join of your payments table with itself to get the "done payments"

BTW, when a payment done, a new record for the next date will generate, but this record will not be shown in the query unless the payment get value !!