function to calculate month ends

[psarros]

I have a database that has data from month ends. Some of the calculations I need to do involve multiple months.

Is there a function that I can use that will calculate month ends?

The DateAdd function works great for the begining of the month, not month ends.....

thanks

 

[JoJ]

Public Function DaysInMonth(intYear As Integer, intMonth As Integer) As Integer

Dim intMonthCal(12) As Integer

intMonthCal(1) = 31
intMonthCal(2) = 28
intMonthCal(3) = 31
intMonthCal(4) = 30
intMonthCal(5) = 31
intMonthCal(6) = 30
intMonthCal(7) = 31
intMonthCal(8) = 31
intMonthCal(9) = 30
intMonthCal(10) = 31
intMonthCal(11) = 30
intMonthCal(12) = 31

' Test for Leap year
If ((intYear) Mod 4 = 0 And (intYear) Mod 100 <> 0) Or _
((intYear) Mod 400 = 0) Then

intMonthCal(2) = 29
End If

DaysInMonth = intMonthCal(intMonth)

End Function

 

[scriverb]

Just use the DateSerial function:
This function calculates the month end for January using hard coded numbers:
DatePart(&quot;d&quot;, (DateSerial(2003, 2, 1) - 1))

Just increment the second parameter(month) and let ACCESS worry about the LeapYear situation. Will something like this work for you. I don't know exactly how you want to use this but it is easy to use the DatePart function of the current date to extract the parts to be used in an expression like this. It can be set up to give you the current months last day using the CurrentDate.

Let me know if you need more help.

Bob Scriver

 

[danvlas]

[DateExpression] - Day(DateExpression)

will return the last day of the month previous to DateExpression.

Day([DateExpression] - Day(DateExpression))
will return the number of days in the month previous to DateExpression

HTH




Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html