472,101 Members | 1,601 Online

# Showing the last day in month Function

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
3 62497
"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
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
"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.

### Similar topics

 reply views Thread by Sebastian | last post: by reply views Thread by Kate | last post: by 2 posts views Thread by Daniel Tan | last post: by 7 posts views Thread by MLH | last post: by 2 posts views Thread by Vijay Balki | last post: by 5 posts views Thread by jenkinsloveschicken | last post: by 3 posts views Thread by paulvonhippel | last post: by 1 post views Thread by Mufasa | last post: by 3 posts views Thread by Coolboy55 | last post: by reply views Thread by isladogs | last post: by reply views Thread by pddon | last post: by reply views Thread by BenitoJuares | last post: by reply views Thread by bakertaylor28 | last post: by reply views Thread by antdb | last post: by 3 posts views Thread by bobbyer | last post: by 2 posts views Thread by Bright1Light | last post: by 7 posts views Thread by bounthong | last post: by 5 posts views Thread by Bright1Light | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.