DateAdd

[Phoebe.]

Hi, Good Day!

Format(DateAdd("m", 11, Mid$(mDate,5,2) + "/" + Right$(mDate,2) + "/" +
Left$(mDate,4)), "dd/mm/yyyy")

The above is part of my formula which to add 11 months to mDate, is there a
way to set the day as the last day for the particular month. For eg.
mDate= 01/01/2003
The new date=01/12/2003
The date that i want it to be show: 31/12/2003

How can it be done since some of the month have 30 days, 29 days and even 28
days.
Can someone help?
Thanks in advanced.

rgds,
Phoebe.

 

[Steve Schapel]

Phoebe,

First of all, your expression to add 11 months is not correct. Is
mDate a Date/Time data type? Or is it a Text field? If it's a date,
for your 11 months you can just use...
DateAdd("m",11,[mDate])
If it's text, you can use...
DateAdd("m",11,CDate([mDate]))

For the last day of the 11th month, try this...
DateSerial(Year([mDate])+1,Month([mDate]),0)

- Steve Schapel, Microsoft Access MVP