Number of days in a given month

  • Thread starter Thread starter Hallgeir
  • Start date Start date
H

Hallgeir

Is there a (easy) way to find the number of days in a month if I have the
date. Example: If the date field is 08.10.04, I want the Daysinmonth field
to show 31.

Thanks in advance!
Hallgeir
 
It would be easier to do this in a function. Paste the
following into a regular module:

Note: No error handling:


Public Function NumberOfDaysInMonth(varDate)
If IsDate(varDate) Then
Dim intYear As Integer
Dim intMonth As Integer
Dim intDay As Integer
Dim dteLastDayOfMonth As Date

intYear = DatePart("yyyy", DateAdd("m", 1, varDate))
intMonth = DatePart("m", DateAdd("m", 1, varDate))
intDay = 1
dteLastDayOfMonth = DateSerial(intYear, intMonth,
intDay) - 1
NumberOfDaysInMonth= Day(dteLastDayOfMonth)
Else
NumberOfDaysInMonth= Null
End If
End Function

The logic goes: Add a month to he day in question, then
get the first of the month. Subtract one day, then figure
out the day part. This method works even for leap years.

Once you have this in a module, you can use it in a query.


DaysInMonth: NumberOfDaysInMonth([MyDateField])


Chris Nebinger


Chris Nebinger
 
The number of days in the month of the field TheDate is:
Day(DateSerial(Year([TheDate]), Month([TheDate]) + 1, 0))

The expression creates a date based on the month after TheDate (month + 1),
day zero. That's the day before the first, so the end of the month. The
Day() function then extracts that day.
 
Back
Top