Function to calc. last day of the month

[Krisse]

Is there a function or a format that gives takes any daate
and gives a date that is the last day of that month?

E.g. 10/15/2003 would give 10/31/2003.

Thanks!

 

[John Viescas]

Here's one way:

DateAdd("m", 1, DateSerial(Year(MyDate), Month(MyDate), 1)) -1

The idea is to add one month to the first day of the current month and then
subtract 1 to get the last day.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/site/TR?pg=personal&fr_id=1090&px=1434411

 

[John Vinson]

Is there a function or a format that gives takes any daate
and gives a date that is the last day of that month?

E.g. 10/15/2003 would give 10/31/2003.

Thanks!

DateSerial(Year([datefield]), Month([datefield]) + 1, 0)

will do the trick. It's sneaky: it's calculating the zeroth day of the
month following, but it will correctly handle the zeroth day of the
thirteenth month, returning December 31.

 

[Rick Brandt]

Is there a function or a format that gives takes any daate
and gives a date that is the last day of that month?

E.g. 10/15/2003 would give 10/31/2003.

DateSerial(Year(MyDate), Month(MyDate)+1, 0)