CODEWITHSUNDEEP
databasedatems-accessexpression
TechEng
TechEng

Reputation: 45

Return monthly due dates for set term based on start date and today's date

I have a table tracking the first_pay_date, term (duration of payments, different for each row) and last_pay_date. I am adding a field, pay_date, that will display the next payment date, assuming that payments are made every month and on time...a payment schedule . I need pay_date field to display the next payment date based on today's date and keep displaying a date until today = last_pay_date/the length of the term. I tried using various IIf statement and this one returns the day based off of todays date but I still need it to return a date every month for the entire term which is in months (i.e. 36), not just 30 days from the first payment date which will never change:

=IIf([first_pay_date]>=Date(),[first_pay_date],DateAdd("d",30,[first_pay_date]))

Maybe an expression is not the way to go and I need a query that creates a temporary table to store payment dates or a combination of the two, possibly even use for and next. I have tried searching for this but cannot find an answer but I feel like this is something that exists I am just not searching correctly. If someone could please point me in the right direction, I would greatly appreciate it. My data is coming from an excel file that I am importing into an access database. I can add fields based on calculations of existing fields but the data in the file is all I have to work so I do not have a received date for payments.

Sample data: 

first_pay_date | last_pay date | term

9/15/2017      | 8/15/2020     | 36

9/5/2017       | 8/5/2019      | 24

Based on today's date my pay_date=9/15/2017 but on Saturday, my pay_date =10/15/2017. On 10/16/2017, I want my pay_date to be 11/14/2017

Upvotes: 1

Views: 51

Answers (1)

Gustav
Gustav

Reputation: 55841

Could be something like:

=IIf(DateAdd("d", 36 * 30, first_pay_date) > Date(), Null, DateAdd("d", (-Int(DateDiff("d", Date(), first_pay_date) / 30) * 30, first_pay_date))

Corrected:

=IIf(DateAdd("d", 36 * 30, first_pay_date) > Date(), Null, DateAdd("d", (-Int(DateDiff("d", Date(), first_pay_date) / 30) + 1) * 30, first_pay_date))

To simulate:

first_pay_date=DateAdd("d", -36 * 30, Date)
today = DateAdd("d", 60, Date)

? IIf(DateAdd("d", 36 * 30, first_pay_date) > today, Null, DateAdd("d", (-Int(DateDiff("d", today, first_pay_date) / 30) + 1) * 30, first_pay_date))

Result: 2017-12-13

Upvotes: 0

Related Questions