Last Day of Month VBA

[michael3426]

I have a database that I'm trying to make some mods to. I have a field with a last test date, score field, and next test date fields. I am trying to modify the VB code to update the next test date field to the last day of the month. For instance, if someone last tested on April 12th, 2015 and scored a 91. I need the next test date to update to the last day of the month to April 30, 2016. This is the code I have been working with but I think I am missing something. Any assistance would be appreciated!

DateSerial(Year(Date), Month(Date + 1), 12)

or

DateSerial(Year(Date), Month(Date + 12), 0)

 

[Ranman256]

vDate = month([testDAte]) & "\1\" & Year([testDate])
nextdate = DateAdd("d",-1,DateAdd("m",1,vDate))

 

[michael3426]

Thanks! This is a good start! What would I have to modify if I want the next date for 6 months?

vDate = month([testDAte]) & "\1\" & Year([testDate])
nextdate = DateAdd("d",-1,DateAdd("m",1,vDate))

 

[Cronk]

I'd suggest you use
DateSerial(Year(testdate) + 1, Month(testdate) + 1, 0) to give end of month in 1 year's time

DateSerial(Year(testdate) , Month(testdate) + 7, 0) for 6 months.