last date of the month

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,
What is the VB code to return the last date of the current month like
11/30/04?
I have tried datediff, dateadd, but don't work.
Thanks
Ed
 
could you please tell me what does 0 mean?
I mean why put 0 at the end for the day?

Thanks
 
Doug has given you a good trick here.

The first day of next month is:
DateSerial(Year(Date), Month(Date) + 1, 1)

Guess what it does if you ask for the day zero?

The date functions in Access are very good at figuring out what is meant by
day zero or month 13. In December, the expression
DateSerial(Year(Date), Month(Date) + 1, 1)
will equate to:
DateSerial(2004, 13, 1)
and Access will correctly recognise that as Jan 1, 05.
 
DateSerial(Year(Date), Month(Date) + 1, 1) would give you the first day of
the next month.

DateSerial(Year(Date), Month(Date) + 1, 1) - 1 would therefore give you the
last day of the current month.

DateSerial is smart enough, though, to handle "unusual" values, so
DateSerial(Year(Date), Month(Date) + 1, 0) is the equivalent to
DateSerial(Year(Date), Month(Date) + 1, 1) - 1

Just as a demo of how flexible DateSerial is, DateSerial(2004, 1, 60) will
give you 29 Feb, 2004, whereas DateSerial(2005, 1, 60) will give you 1 Mar,
2005. DateSerial(2004, 0, 0) will give you 30 Nov, 2004 (the 0 for month
will give you the last month of the previous year, and the 0 for day will
give you the last day of the previous month)
 
Back
Top