First and Last Day Of Month

Printable View

Mar 26th, 2005, 07:05 AM
vbcoder2

First and Last Day Of Month

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. .... :wave:
Nov 7th, 2006, 01:36 PM
Chrissy

Re: First and Last Day Of Month

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?
Nov 8th, 2006, 05:34 AM
Mark Gambo

Re: First and Last Day Of Month
Well the first day of the Month should be the first, right :confused: ?
and you could use the following to find the last day of the month:
VB Code:
```
Dim dtDate as Date
dtDate = "09/09/2005"
 
DateAdd("m",DateSerial(Year(dtDate), Month(dtDate), 1) - 1
```
Nov 8th, 2006, 05:37 AM
Mark Gambo

Re: First and Last Day Of Month

Here is another LINK that finds the number of days in a month
Nov 8th, 2006, 07:35 AM
shakti5385

Re: First and Last Day Of Month

Quote:

Originally Posted by Mark Gambo

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.
Nov 8th, 2006, 10:20 AM
Mark Gambo

Re: First and Last Day Of Month

Quote:

Originally Posted by shakti5385

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.
Nov 8th, 2006, 10:29 AM
Mark Gambo

Re: First and Last Day Of Month
But here is the CR Formula:
VB Code:
```
DateAdd("m",1,DateSerial(Year({Table1.dtDate}), Month({Table1.dtDate}),1))-1
```
@ Shakti - Looks like my code in post #3 except without the Variable.
Nov 8th, 2006, 10:46 AM
Mark Gambo

Re: First and Last Day Of Month

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
Nov 9th, 2006, 05:42 AM
ganeshmoorthy

Re: First and Last Day Of Month

@Mark Gambo, if you find the last day of the month that itself will tell you the no of days in that month...lol... :confused:
Nov 9th, 2006, 06:35 AM
Mark Gambo

Re: First and Last Day Of Month

Quote:

Originally Posted by 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... :confused:

Huh?? :confused: :confused:
Nov 9th, 2006, 06:42 AM
shakti5385

Re: First and Last Day Of Month

Quote:

Originally Posted by Mark Gambo

Huh?? :confused: :confused:

He was saying that in the VB, DTPicker.Day Return the last day of the month. :wave:
Nov 9th, 2006, 07:36 AM
Mark Gambo

Re: First and Last Day Of Month

Quote:

Originally Posted by shakti5385

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

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?
Nov 9th, 2006, 07:47 AM
shakti5385

Re: First and Last Day Of Month

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.
Apr 5th, 2007, 02:28 AM
shakti5385

Re: First and Last Day Of Month
Quote:
Originally Posted by Mark Gambo

But here is the CR Formula:

VB Code:

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.
Jul 3rd, 2009, 10:02 PM
winwin

Re: First and Last Day Of Month

'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
Jul 3rd, 2009, 10:05 PM
winwin

Re: First and Last Day Of Month

'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
May 7th, 2016, 09:02 PM
aab1

Re: First and Last Day Of Month
Quote:
Originally Posted by Mark Gambo

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

VB Code:

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.
May 9th, 2016, 09:56 AM
FunkyDexter

Re: First and Last Day Of Month
Yeah it looks like something went wrong there.

The idea is that you use DateSerial to get 1st day of the month:-
VB Code:
```
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
VB Code:
```
DateAdd("d", -1, DateAdd("M", 1, DateSerial(Year(dtDate), Month(dtDate), 1)))
```