Function to return the last day of the month 1

[jaaret]

I'm attempting to write a function that returns the last day of the month for any given date. My current attempt is generating a type mismatch error.

Here's my attempt:

Function LastDayOfMonth(DateMonth As Date) As Date

Dim MonthStr As String
Dim YearStr As String
Dim MonthInt As Integer

MonthStr = Month(DateMonth)
YearStr = Year(DateMonth)
MonthInt = Month(DateMonth)

Select Case MonthInt
Case 1
LastDayOfMonth = "#" & MonthStr & "/31/" & YearStr & "#"
Case 2
LastDayOfMonth = "#" & MonthStr & "/28/" & YearStr & "#"

Etc...

When I change the function's first line to:

Function LastDayOfMonth(DateMonth As Date) As String

It returns the correct string, i.e: #12/31/2006#

But when I try:

Function LastDayOfMonth(DateMonth As Date) As Date

It errors.

Thanks in advance for the help,
Jaaret

 

[PHV]

Why not simply this ?
Function LastDayOfMonth(DateMonth As Date) As Date
LastDayOfMonth = DateSerial(Year(DateMonth), Month(DateMonth) + 1, 0)
End Function

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[jaaret]

Thanks! Much simpler. I haven't used the DateSerial function.

Jaaret