Displaying a date at the start of the following month...

[Jamescdawson]

I generate a report which is a Customer Payment Schedule for clients and shows the client when his loan was paid out and when the first repayment is due.

Until now, we have shown the start date and displayed the first payment due one month later i.e. [LoanStartDate] +30.

However, I now need the first payment date to be the first day of the next full month. (For example a new loan paid out on Feb 15th 2016 would (in the past) have required the first payment to be made on March 15th 2016. [LoanStartDate] +30, when [LoanStartDate] = 2/15/16.) But we now need that first payment date to show as "April 1st 2016". (i.e. the first day of the next full month)

Question: How do I get my "first payment due" field to show a date which reflects this requirement. Is there a function hiding somewhere I can use for this?



=[LoanStartDate] + ????

Many Thanks

[ssanfu]

What should the date be if the loan start date is the first of the month ie 2/1/2016 or 3/1/2016?



This function seems to work

Function FirstPayment(pInputDate As Date)
    '  Return a date that is the first day of the month of the date passed
    Dim d As Integer, M As Integer, Y As Integer
    Dim mths As Integer

    mths = 2

    If IsNull(pInputDate) Then
        FirstPayment = Null
    Else
        'sets the number of months
        mths = mths + (Day(pInputDate) = 1)

        d = Day(pInputDate)
        M = Month(pInputDate)
        Y = Year(pInputDate)
        FirstPayment = DateSerial(Y, M + mths, 1)
    End If
End Function

In the immediate window, I get this

? FirstPayment(#2/15/2016#)   <- enter this
4/1/2016         <--returns this

Usage would be for a control
= FirstPayment([LoanStartDate])

In a query PmntDate: FirstPayment([LoanStartDate])

[Jamescdawson]

Wow Steve.... That was quick. Thanks

I guess if the loan start date was 1st of the month, then the the first payment date should probably be 1st of the following month !!

Your solution is a little more complex than I had expected, but I'll try it tomorrow.

Many Thanks

Jimbo

[ssanfu]

Create a standard module (not a form module) and paste in the function above. Ensure the first two lines of the module are:

Option Compare Database
Option Explicit

Then just use the function as described. Easy.

[Jamescdawson]

Steve,

A bit late coming back to you, but Thanks... your functions works great (easy!!... as you said)

Jimbo

[ssanfu]

No problems... Glad I was able to help.

Good luck on your project.