Access Tip – Calculating the number of days in a month

This is a simple function that allows you to find the number of days in a month. Pass the date to the function and it will return the number of days. Leap years are accounted for.

Assume you have a variable called intDaysInMonth and a date called dteMyDate. To call the function use this:

intDaysInMonth = funDaysInMonth(dteMyDate)

if dteMyDate were 8 Jan 2009, intDaysInMonth would be 31.

One point is worth noting. Since the Mid function starts counting from one, you need to start the string with a number that will never be returned. It can be anything but we have used 0.

For January which is month 1, we start counting from 2 ( Month(dteDate) * 2). 2 is the second character. We need to put a filler in position one.

‘—————————————————————————————
‘ Procedure : funDaysInMonth
‘ Author : Neville Turbit
‘ Date : 28/05/09
‘ Purpose : Calculate the number of days in a month.
‘—————————————————————————————
‘
Public Function funDaysInMonth(dteDate) As IntegerOn Error GoTo Error_funDaysInMonth

funDaysInMonth = Mid(“0312831303130313130313031”, Month(dteDate) * 2, 2)

‘ Leap year
If (((Year(dteDate) Mod 4) = 0) And (Month(dteDate) = 2)) Then funDaysInMonth = 29

Exit_funDaysInMonth:
On Error GoTo 0
Exit Function

Error_funDaysInMonth:

MsgBox “An unexpected situation arose in your program.” & funCrLf & _
“Please write down the following details:” & funCrLf & funCrLf & _
“Module Name: modGeneric” & funCrLf & _
“Type: Module” & funCrLf & _
“Calling Procedure: funDaysInMonth” & funCrLf & _
“Error Number: ” & Err.Number & funCrLf & _
“Error Descritption: ” & Err.Description

Resume Exit_funDaysInMonth
End Function

Your Content Goes Here