calculate days in month 1

[meinhunna]

how can i calculate number of days in current month using access query

 

[jbpez]

I found this code at a site awhile ago, but I can't remember where. Anyways add this function into a module and reference it from a query.

Function GetDaysInMonth(dteInput As Date) As Integer
On Error GoTo ErrHandler
    
    Select Case Month(dteInput)
        Case 2
            GetDaysInMonth = 28
            Dim iYear As Integer
            iYear = Year(dteInput)
            If iYear Mod 4 = 0 Then
                If iYear Mod 100 = 0 Then
                    If iYear Mod 400 = 0 Then
                        GetDaysInMonth = 29
                    End If
                Else
                    GetDaysInMonth = 29
                End If
            End If
        Case 4, 6, 9, 11
            GetDaysInMonth = 30
        Case Else
            GetDaysInMonth = 31
    End Select
    
Exit Function

John Borges

 

[PHV]

No need for UDF:
Day(DateSerial(Year(Now),Month(Now)+1,0))

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[meinhunna]

why have u added 1 to month???

 

[r937]

because he's using day 0 of the next month to find the last day of the current month

very sneaky, phv

;-)

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts March 6 2005)

 

[PHV]

very sneaky, phv
Hey, it's not me but VBA ...

 

[r937]

using month+1 and day 0 is definitely sneaky

less sneaky would be

Day(
DateAdd("d",-1,
DateAdd("m",1,
DateSerial(Year(Now),Month(Now),1))))

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts March 6 2005)