dateadd()

[Keel]

I am trying to use the DateAdd function to find the last day of any month. I am using code that I saw on Microsoft's VB page. This is the code:


Dim TEMP2 as date
Dim nLastDay as Integer
RptDateBeg = DatePart("m", date) & "/01/" & DatePart("yyyy", date) & " 12:00:00 AM"
TEMP2 = RptDateBeg
nLastDay = DatePart("d", DateAdd("M", 1, TEMP2 - DatePart("d", TEMP2)))




The problem is for months with 31 days that follow months with 30 days it tells me the last day is the 30th instead of the 31st. It works with 30 day months. For example, for October it is giving me 10/30/01 as the last day. Any suggestions?

[ramayansunil]

Hi,
Try doing with this code:

rptdatebeg = DatePart("m", Date) & "/01/" & DatePart("yyyy", Date) & " 12:00:00 AM"
TEMP2 = rptdatebeg
nLastDay = DatePart("d", DateAdd("d", 30, TEMP2))

If nLastDay=1 then the month is having 30 days.
If nLastDay=31 then the month is having 31 days.
If nLastDay=3 then the month is having 28 days(Feb).
Have a nice time.
sunil.

[Cakkie]

I think this code is a little cleaner, get the first day of the month, add a mont to get the first day of the next month, then subtract one day. To get the last day of the month. This works, whatever month it is, howmany days that month may have.

Dim TEMP2 as date
Dim nLastDay as Integer
RptDateBeg = DatePart("m", date) & "/01/" & DatePart("yyyy", date) & " 12:00:00 AM"
TEMP2 = RptDateBeg
nLastDay = DatePart("d", DateAdd("d",-1,DateAdd("M", 1, TEMP2)))







Tom Cannaerts
slisse@planetinternet.be

Programming today is a race between software engineers striving to build bigger and better idiot-proof programs, and the universe trying to produce bigger and better idiots. So far, the universe is winning -- Rich Cook

[Iouri]

Function EndOfMonth (D As Variant) As Variant
EndOfMonth = DateSerial(Year(D), Month(D) + 1, 0)
End Function

Lastofmonth = DateAdd("m", 1, Date - Day(Date))


Iouri Boutchkine
iouri@hotsheet.com

[John G Duffy]

The eastiest way to find the last day of the month is to go to the first day of the next month then back up one day as this simple example shows

private Sub Command1_Click()
Dim strDate as date
strDate = "11/01/2001"
MsgBox strDate - 1
End Sub




John G