Return last date in a month plus one year

[Grunners]

All,

This is proving tricky! I've got a date - say 21/07/2006.

What I want is to be able to return 31/07/2007 - i.e. whatever the date go to the end of the month and then add one year on to that value. Adding the year on is easy (even for me) but getting to the end of the month...

Some more examples:

23/06/2006 should return 30/06/2007
01/01/2001 should return 31/01/2002
30/10/2003 should return 31/10/2004

Any ideas much appreciated!

 

[RuralGuy]

Try: DateSerial(Year(YourDate)+1,Month(Yourdate)+1,0)

 

[Grunners]

Pretty good!

Many thanks RG - that worked perfectly.

I had a sneaking suspicion DateSerial was the way to go but was just about to start a procedure that took the number of days in the first two characters of the date from the total number of days in the month and then use DateAdd to add that value on to the first and then add another 365 on. Jeez...

This works a treat - Thanks again.

 

[RuralGuy]

Glad I could help.