Number of days in a given month

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


-----Original Message-----
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


.

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.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Hallgeir" wrote in message
...
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