Showing the last day in month Function

[Gol Yass]

Dear Newsgroup,

Is there a function in Access XP to take a date or month , and return the
last day in that month?
Ex: the last day in september is 30 or the last day in May is 31 .
Thank you very much,
Gol

[Fletcher Arnold]

"Gol Yass" <go********@yahoo.com> wrote in message
news:bj*********@news.emirates.net.ae...

Dear Newsgroup,

Is there a function in Access XP to take a date or month , and return the
last day in that month?
Ex: the last day in september is 30 or the last day in May is 31 .
Thank you very much,
Gol

You will have to write your own function, but if you remember that the last
day of any month occurs 1 day before the first of the following month, it's
easy.

Eg This function takes any date and returns the last day of the month of
that date:

Function LastDay(dteAny As Date) As Date
LastDay = DateSerial(Year(dteAny), Month(dteAny) + 1, 1) - 1
End Function
HTH

Fletcher

[Jon Trelfa]

Gol Yass wrote:

Dear Newsgroup,

Is there a function in Access XP to take a date or month , and return the
last day in that month?
Ex: the last day in september is 30 or the last day in May is 31 .
Thank you very much,
Gol

I use this function, which just returns the number (i.e. 29, 31, 30):

daysInMonth = Day(DateAdd("d", -1, DateSerial(Year(DateVar),
Month(DateVar) + 1, 1)))

Of course, it's probably the "long way"...

--
Jon Trelfa
There 10 kinds of people in this world...
Those who understand binary and those who don't

[Douglas J. Steele]

"Merle Nicholson" <me******@tampabay.rr.com> wrote in message
news:QK******************@twister.tampabay.rr.com. ..

Here's what I use.
Public Function LastDayInMonth(ByVal AnyDate As Date) As Date
' ************************************************** **********
' Given any valid date, returns the last day of that month
' *****************************
LastDayInMonth = DateAdd("m", 1, CDate(Month(AnyDate) & "/01/" &
Year(AnyDate))) - 1
End Function

Merle: I'd strongly suggest changing your function to:
Public Function LastDayInMonth(ByVal AnyDate As Date) As Date
' ************************************************** **********
' Given any valid date, returns the last day of that month
' *****************************
LastDayInMonth = DateAdd("m", 1, DateSerial(Year(AnyDate),
Month(AnyDate), 1) - 1
End Function

The CDate function respects the user's date format settings. Anyone using
your function who has their Short Date set to dd/mm/yyyy will get incorrect
results.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele