By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,107 Members | 1,284 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,107 IT Pros & Developers. It's quick & easy.

Showing the last day in month Function

P: n/a
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
Nov 12 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
"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
Nov 12 '05 #2

P: n/a
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

Nov 12 '05 #3

P: n/a
"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


Nov 12 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.