How to Calc Ending Day of a Month

[Guest]

Given 30 days hath September, April, June, and November;
All the rest are 31, except February which can be 28 or 29.

Is there an Access date function that will yield the last day of any month,
including in leap years?

If not, how can it be constructed?
Do we have to build our own table in a module, and if so, how do we account
for leap years?

I didn't spot the last day of a month in the datetime section (but it could
be there).

 

[Bill]

Mike,

This may work for you.

Function Date_of_last_Day_of_this_month(input_Date As Date) As Date

Dim Date_Plus_1_month As Date, Day_of_month As Integer

Date_Plus_1_month = DateAdd("m", 1, input_Date)

Date_of_last_Day_of_this_month = DateAdd("d", -day(Date_Plus_1_month),
Date_Plus_1_month)

End Function


It will return the last date of the month for what ever date you provide for
input date. Feed that into the day function to get a number representing the
day of the month if you need that.

myday = date(Date_of_last_Day_of_this_month(Date)

Hope this helps.

Bill

 

[Rick Brandt]

Given 30 days hath September, April, June, and November;
All the rest are 31, except February which can be 28 or 29.

Is there an Access date function that will yield the last day of any month,
including in leap years?

If not, how can it be constructed?
Do we have to build our own table in a module, and if so, how do we account
for leap years?

I didn't spot the last day of a month in the datetime section (but it could
be there).

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

 

[Guest]

Thanks Bill and Rick.

I'll try those functions.

MichaelM