First and Last Day Of Month

[vbcoder2]

Hi Friends I need to Know the First and Last Day of Month . The PArameter is of Month Code.... i m using Seagate Crystal Report 8. ....

[Chrissy]

Hi there.. I was justing searching the forums to find a way to get the last day in any given month in Crystal Reports.

Did you ever figure that out?

[Mark Gambo]

Well the first day of the Month should be the first, right ?
and you could use the following to find the last day of the month:

Dim dtDate as Date
dtDate = "09/09/2005"
 
DateAdd("m",DateSerial(Year(dtDate), Month(dtDate), 1) - 1

[Mark Gambo]

Here is another LINK that finds the number of days in a month

[shakti5385]

Here is another LINK that finds the number of days in a month

I thought they want the formula in the CR not in the VB.

[Mark Gambo]

I thought they want the formula in the CR not in the VB.

Hmmm, maybe so but with the examples I supplied the op should be able to create the CR Formula.

[Mark Gambo]

But here is the CR Formula:

DateAdd("m",1,DateSerial(Year({Table1.dtDate}), Month({Table1.dtDate}),1))-1

@ Shakti - Looks like my code in post #3 except without the Variable.

[Mark Gambo]

Here are a few other links I found:

http://www.tek-tips.com/faqs.cfm?fid=1960
http://www.crystalreportsbook.com/Cr...I.asp?Page=7_3

[ganeshmoorthy]

@Mark Gambo, if you find the last day of the month that itself will tell you the no of days in that month...lol...

[Mark Gambo]

@Mark Gambo, if you find the last day of the month that itself will tell you the no of days in that month...lol...

Huh??

[shakti5385]

Huh??

He was saying that in the VB, DTPicker.Day Return the last day of the month.

[Mark Gambo]

He was saying that in the VB, DTPicker.Day Return the last day of the month.

If you are referring to Post # 4, I just included it because it was germane to the discussion and some of us don't use the DatePicker Control in their apps. BTW, where in this thread was the DatePicker control discussed?

[shakti5385]

I am making the formula in the CR related to it. And I am not discussing here on the VB property like dtpicker, the people here related on the reporting post, let wait for the thread starter reply.
Dtpicker is not a metter here.

[shakti5385]

But here is the CR Formula:

DateAdd("m",1,DateSerial(Year({Table1.dtDate}), Month({Table1.dtDate}),1))-1

@ Shakti - Looks like my code in post #3 except without the Variable.

This is also not giving the result, In CR it dispalying the Date only not the last day of Month.

[winwin]

'try this......... 2 DTpicker set to First and Last day of the month



Private Sub Form_Load()
DTPicker1.Value = DateAdd("d", -(Day(Date) - 1), Date)
DTPicker2.Value = DateAdd("d", (getNumberOfDays - 1), DTPicker1.Value)
End Sub
'Get the number of days each month is having
Public Function getNumberOfDays() As Integer
Select Case DateTime.Month(Date)
Case 1, 3, 5, 7, 8, 10, 12
getNumberOfDays = 31
Case 4, 6, 9, 11
getNumberOfDays = 30
Case 2
'logic for checking leap years
If (Year(Date) Mod 4) = 0 Then
If (Year(Date) Mod 100) = 0 Then
If (Year(Date) Mod 400) = 0 Then
getNumberOfDays = 29
Else
getNumberOfDays = 28
End If
Else
getNumberOfDays = 29
End If
Else
getNumberOfDays = 28
End If
End Select
End Function

[winwin]

'try this......... 2 DTpicker set to First and Last day of the month



Private Sub Form_Load()
DTPicker1.Value = DateAdd("d", -(Day(Date) - 1), Date)
DTPicker2.Value = DateAdd("d", (getNumberOfDays - 1), DTPicker1.Value)
End Sub

'Get the number of days each month is having
Public Function getNumberOfDays() As Integer
Select Case DateTime.Month(Date)
Case 1, 3, 5, 7, 8, 10, 12
getNumberOfDays = 31
Case 4, 6, 9, 11
getNumberOfDays = 30
Case 2
'logic for checking leap years
If (Year(Date) Mod 4) = 0 Then
If (Year(Date) Mod 100) = 0 Then
If (Year(Date) Mod 400) = 0 Then
getNumberOfDays = 29
Else
getNumberOfDays = 28
End If
Else
getNumberOfDays = 29
End If
Else
getNumberOfDays = 28
End If
End Select
End Function

[aab1]

Well the first day of the Month should be the first, right ?
and you could use the following to find the last day of the month:

Dim dtDate as Date
dtDate = "09/09/2005"
 
DateAdd("m",DateSerial(Year(dtDate), Month(dtDate), 1) - 1

I know this is an old thread but there's a bug in the above code, there are 4 opening parenthesis but only 3 closing ones. I'm trying to understand how it works but with the bug/missing parenthesis it's hard to understand.

[FunkyDexter]

Yeah it looks like something went wrong there.

The idea is that you use DateSerial to get 1st day of the month:-

DateSerial(Year(dtDate), Month(dtDate), 1)

To get the last day of the month you add a month to the first day then subtract 1 day which gives you

DateAdd("d", -1, DateAdd("M", 1, DateSerial(Year(dtDate), Month(dtDate), 1)))