Looping in the table to check the Rent End Date.

[Spidey]

Hi,

I like to create a program to keep tracks of the rental payment and I like to have the monthly receipt automatically generated when the date is near. Is there an efficient way do it other than looping through all the contract table? Thanks.

Kent

[eos]

Does “keeping track” mean something along the lines of: Alert me when it's (say) three days before the first of the next month (aka “the date”), so I can click on a button, go to all (active) Contracts records, using a Receipt print layout, and have a receipt printed for each of my contracts for the coming month; and also have a date flag set on each contract, so I can see it's been done for this month, until about 30 days later the fun starts again?

EDIT: Forgot your looping concerns. Get back to you on that one.

[Spidey]

Thank you for your reply. Yes. Each contract for example has a start date and an end date. For example, Jan 2012(start date) to Jan 2014(end date), and will have a total of 36 receipts What I like to do is that when it is 4 days b4 the due date of the rent it due, it will automatically create a receipt for this particular month only.

If the contract doesn't have an end date, the tenant can live there as long as he knows when he likes to leave.

I was thinking if there are too many tenants, it will take too long to loop each contract if there are many tenants. Just hope if there is a more efficient way.

Many thanks.

K.C.

[comment]

Why don't you find the active contracts, then import them into the Receipts table?

"Active contracts" would be those where StartDate < today AND EndDate > end of current month OR EndDate is empty (I think?).

[eos]

If creating a receipt means creating a record in a Receipts table and print that one, then follow Comment's advice: Find the active contracts, then import them into the Receipts table and print from there, using the print option “Records being browsed”.

If on the other hand you don't have (or plan to have) a Receipts table, but just a print layout in Contracts, then find and print them there.

Either way, you don't need a loop for printing.

I think that if K. C. would like to be alerted shortly before the rent is due, then a contract that starts the following month (i.e. a few days later) would also have to be considered “active”, so startDate < today wouldn't always work.

Does ( startDate < today OR startDate = start of next month ) AND ( endDate > end of current month OR endDate is empty ) sound right?

K. C., can you get started from these ideas?

[Spidey]

Thank you Comment and EOS for your help. :)