See First Day of Month

Len
Just by your description I'm guessing that your due date is the last day of the month.  Correct?

If that is true, I would base your DateAdd on the 1st Day of next month then subtract one day. Remember ALL months have a 1st of the month but the last day varies (30,31, 28, and sometimes 29 in a leap year).
Give it a shot and see what you come up with.  You'll remember it better if you solve on your own, but if you hit a wall post your formula and we'll help you through it.

Re-watch the Access Expert 28 lessons if you need a refresher.

Sorry guys I did not explain enough. There is not just one due date. There are a number of clients and each have their own due date depending on when they started renting. So the due date can be any day of the month.

Do you store the clients start date anywhere?  Could you use DLookup to get the Day of renewal? Then use DateSerial to calculate the next date.

Thanks Scott. Sounds like A plan. I will give it a try and let you know

Hi, I Have a 'TenantSince' field which is the start date and creates the monthly payment date. After going back through Expert 28
and other tutorials I managed to create this query. Creating a new dummy tenant who started on the 30th December 22.I ran the updates andGot these results. As you will see it works to start with, but makes the Feb payment in March and for obvious reasons then jumps to April. I cannot figure whats wrong, I'm hoping it's obvious to you guys as my head is buzzing! Images to follow

Try doing it like in my screenshot below.

Thanks Kevin, that looks good, I will give it a try

I've now changed from a query to the code kindly posted by Kevin. But although I'm now getting the Feb update as expected March still updates based on the Feb date. see list

Cannot figure out why it will not work. These dates are affecting the rental period calculations, which causes errors on payment receipts, remittance advice and other reports. I think I will try to correct March & future dates by DLookUp of the day number of the the first ever rent payment after the above code has run!

I am now getting the correct dates, but only by a correction routine if the date is wrong in month 3. This seems a bit of a bodge.
There must be a better way. Has this problem not come up before?