Thanks raskew, you just solved a problem [wish list] that I have had on the backburner.
Sub Test()
NumberOfDaysInMonth ("2/01/2000")
End Sub
Public Function NumberOfDaysInMonth(dDate As Date)
Dim NumberOfDays As Integer
NumberOfDays = Day(DateSerial(Year(dDate), Month(dDate) + 1, 0))
MsgBox NumberOfDays
End Function
Correctly returns 29. February 2000 was a leap year!
When using date functions provided by others, it's important to diligently test the functions across several sets of data (or dates, in this case). Just because a function seems to works properly/accurately/error-free on today's date, that doesn't mean it will be accurate for the whole year.
For example, one of the other solutions starts by adding "1" to the month number, the get the date of the 1st of the next month. That's fantastic... unless it's December. (unless there's a new "month #13" I'm unaware of!)
When a project uses a lot of date or math functions that aren't available in Access but are in Excel, I prefer to call Excel's built-in functions from Access VBA instead of trying to recreate them.
The function below uses the WorksheetFunction object in Excel's library to return the number of days in the month of the date specified. (Since it's late-bound, no references are required.)
Code:
Option Explicit
Public xl As Object
Function daysInMonth(dt As Date) As Integer
'Returns the number of dates in the month of [dt]
'create Excel object - but only if one doesn't already exist
If xl Is Nothing Then Set xl = CreateObject("Excel.Application")
'get the last day of the month for the specified date
daysInMonth = Day(xl.WorksheetFunction.EOMonth(dt, 0))
End Function
Sub demo()
MsgBox "There are " & daysInMonth(Now()) & " days in this month."
End Sub
Two items of note regarding this thread...
First, it is getting close to heading off to university.
Second, DateSerial is smart enough to return the proper date when you pass it a month greater than 12. It does go to the next year without issue. It is rather bright about these things and can even handle being passed a negative value. It doesn't like Null, but that is rather to be expected.
When using date functions provided by others, it's important to diligently test the functions across several sets of data (or dates, in this case). Just because a function seems to works properly/accurately/error-free on today's date, that doesn't mean it will be accurate for the whole year.
For example, one of the other solutions starts by adding "1" to the month number, the get the date of the 1st of the next month. That's fantastic... unless it's December. (unless there's a new "month #13" I'm unaware of!)
When a project uses a lot of date or math functions that aren't available in Access but are in Excel, I prefer to call Excel's built-in functions from Access VBA instead of trying to recreate them.
The function below uses the WorksheetFunction object in Excel's library to return the number of days in the month of the date specified. (Since it's late-bound, no references are required.)
Code:
Option Explicit
Public xl As Object
Function daysInMonth(dt As Date) As Integer
'Returns the number of dates in the month of [dt]
'create Excel object - but only if one doesn't already exist
If xl Is Nothing Then Set xl = CreateObject("Excel.Application")
'get the last day of the month for the specified date
daysInMonth = Day(xl.WorksheetFunction.EOMonth(dt, 0))
End Function
Sub demo()
MsgBox "There are " & daysInMonth(Now()) & " days in this month."
End Sub