DateAdd advice please

[LEXCERM]

Hi all,

I have a start date of, say, 31st October and I want to add forthcoming monthly dates to a table for the 31st of each month.

My inital sequence is:

StartDate = #31/10/2015#

Do
IncrementDate = DateAdd("m", 1, StartDate)
'''stuff
Loop

What happens is that the next date is the 30th November, which is fine as it takes into account that November only has 30 days.

But the following date after that returns 30th December, rather than 31st. Also worried about leap years.

What is the best method to handle this?

Many thanks in advance

 

[Minty]

Have a look here http://www.access-programmers.co.uk/forums/showthread.php?t=163153

 

[LEXCERM]

[RESOLVED] Re: DateAdd advice please

Think I've found the solution. Something like this:-

iIncrement = iIncrement + 1
IncrementDate = DateAdd("m", iIncrement, StartDate)

DateAdd does the rest!

Thanks and regards.

 

[LEXCERM]

Thanks Minty for the feedback.

 

[arnelgp]

StartDate = #31/10/2015#
StartDate = DateAdd("m", 1, StartDate)

NextDate = LastOfMonth(StartDate)



Function LastOfMonth(InputDate As Date)
' Return a date that is the last day of the month of the date passed
Dim D As Integer, M As Integer, Y As Integer

D = Day(InputDate)
M = Month(InputDate)
Y = Year(InputDate)

'find the first day of next month, then back up one day
LastOfMonth = DateAdd("m", 1, DateSerial(Y, M, 1)) - 1

End Function

 

[spikepl]

LastDayInMonth = DateSerial( Year(dtmDate), Month(dtmDate) + 1, 0)