getting last day of a month 2

[BillNielsen]

I know I've seen a function that returns the last day of a month before but I can't seem to find it. I've created a function using an array (31,28,31,30...) but someone mentioned to me there is even easier method. Does anyone have any code on this?

Thanks,
Bill N

 

[Maquis]

Here is how I do it. I'm not sure that it is the most efficient way either.


Function LastDay(ByVal month) 'pass in mm-yyyy
Dim NextMon As Integer
Dim Year As Integer

Year = Right(month, 4)
NextMon = Left(month, 2) + 1
If NextMon = 13 Then
NextMon = 1
Year = Year + 1
End If
LastDay = CDate(NextMon & "-01-" & Year) - 1

End Function


You can re-write the function to accept whichever date format is most convenient for you. I already had my dates in mm-yyyy format so this worked for me.

 

[BillNielsen]

Thanks Marquis but I think I found an easier way.

Since Access is very forgiving it will round an ill formated date to its closest correct date. Hence if you pass the 0 date of the next month, Access will return the last day of the last month. Hence the code:

dim dteLast as Date
dteLast=DateSerial(Year(date()),Month(Date()+1),0)

Thanks,
Bill N

 

[LonnieJohnson]

Thanks Bill,

I was referred to this from the General Discussions group. It is just what I needed.

B-) ljprodev@yahoo.com
ProDev, MS Access Applications

http://www.galaxymall.com/software/PRODEV

 

[Guest_imported]

This code works great, just thought I would point out a correction. The +1 in the above example was for the day, but it should be for the month, like this:


dim dteLast as Date
dteLast=DateSerial(Year(date()),Month(Date())+1,0)

 

[MichaelRed]

Some aparent confusion here.

Since Access is very forgiving it will round an ill formated date to its closest correct date.

Not really. It is SIMPLE and very basic math. You may include any valis expression which evaluates to numeric entries which collecively results in a date within the range of date data types. This is well documented in the help system, even to the actual use of an expression for the DAY which results in a Zero. Further, attempts to use Zero as the day in most (ALL?) other date functions will result in an error.

The concept here is just that specifying the "Zeroth" day of some month is just the same as specifying the day before the 1st day of the same month.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[PurpleUnicorn]

I found this function online - I can't find the link. It works very well.

Function FindEOM(varDate)

Dim NextMonth, EndOfMonth
NextMonth = DateAdd("m", 1, varDate)
EndOfMonth = NextMonth - DatePart("d", NextMonth)
FindEOM = EndOfMonth

End Function

Nancy