count days

M

manolakshman

How do you readily come to know by a function the number of days in a month.
It can be done by DateDiff(), but that seems to be more complicated.
 
F

Flick Olmsford

Using Access 2003, I don't know of any function that does what you are
looking for. You might try what I listed below, but it sounds more
complicated than you are looking for.

select case month_num
case 9, 4, 6, 11 "30 days has sept, april, jun and november
code for 30 days
case for all others but month num = 2
code for 31 days
case else
code for Feb, taking leap year into account
end select
 
K

Klatuu

This expression will show you the last day of the month for the date you give
it. That will, of course, be the number of days in that month. The example
uses the current date. You can pass it any legimate date:

day(dateserial(year(date),month(date)+1,0))
 
D

Dirk Goldgar

manolakshman said:
How do you readily come to know by a function the number of days in a
month.
It can be done by DateDiff(), but that seems to be more complicated.
Click to expand...


Because of leap years, this can't really be determined without considering
the year. Here's a function that accepts a date, a month, or a month and
year, and returns the number of days in the month:

'----- start of code -----
Function fncDaysInMonth(pDateOrMonth As Variant, Optional pYear As Variant)
As Variant

' Given a date, or month and year, return the number of days in the
month.
' If month is given but year is not, then the current year is used.
'
' Usage examples:
' ?fncDaysInMonth(#12/1/2008#)
' 31
' ?fncDaysInMonth(6)
' 30
' ?fncDaysInMonth(2, 2007)
' 28
' ?fncDaysInMonth(2, 2008)
' 29
'
' Written by: Dirk Goldgar on: 21 August, 2008

Dim dtDate As Date
Dim intYear As Integer

If IsDate(pDateOrMonth) Then
dtDate = pDateOrMonth
fncDaysInMonth = Day(DateSerial(Year(dtDate), Month(dtDate) + 1, 0))
Else
If IsNull(pDateOrMonth) Then
fncDaysInMonth = Null
Else
If IsMissing(pYear) Or IsNull(pYear) Then
intYear = Year(Date)
Else
intYear = pYear
End If
fncDaysInMonth = Day(DateSerial(intYear, pDateOrMonth + 1, 0))
End If
End If

End Function
'----- end of code -----
 
J

John Spencer

This expression works for any date from 100 AD on.

Day(DateSerial(Year(SomeDate),Month(SomeDate + 1, 0))



John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
 
D

Dirk Goldgar

John Spencer said:
This expression works for any date from 100 AD on.

Day(DateSerial(Year(SomeDate),Month(SomeDate + 1, 0))
Click to expand...


You're missing a parenthesism, but otherwise that's the same expression my
function uses when a date is passed.
 
E

evajen01

Hi,

I think your best option is to use DateDiff(). You could create a
user-defined function if you want to call it repeatedly. Another option,
depending on what you are using it for, would be to create a table that
stores the number of days in each month, and then just refer to the table
when you need the answer. One would think that their would be a pre-defined
function for this, but I have not run across one.

Good luck.
 
Top