To find Month and Days in given date period

[waqas]

dear experts.

i want to get month and how many days of each with in given date period.

like 04-jan-13 to 15-Mar-13.

result should like below.

Month Days

Jan-13 28
Feb-13 28
Mar-13 15

how can i get this information

[Missinglinq]

You don't say where you want this to end up (in a Textbox, in a Variable, etc) or what you want to use it for, but here's an example using Textboxes named StartDate, EndDate, TargetField (where I'm placing the results) and a Command Button named cmdMonthsAndDays. The results given is a String. You could use another Control/Event to trigger the calculations, depending on your needs, but this is the general idea:

Private Sub cmdMonthsAndDays_Click()
 
  
 Dim Beginning As Date
 Dim Ending As Date
 Dim TotMonths As Integer
 Dim Mmonth As String
 Dim DisplayMonth As Date
 Dim PrintMonth As String
 Dim DaysByMonth As String
 Dim FinalResults As String
 
 If Nz(Me.StartDate, "") = "" Or Nz(Me.EndDate, "") = "" Then
  MsgBox "You must Enter a Start Date and an End Date!"
  Exit Sub
 End If
 
 Beginning = Me.StartDate
 Ending = Me.EndDate
 
 TotMonths = DateDiff("m", Beginning, Ending) + 1

 For x = 0 To (TotMonths - 1)
  
  Mmonth = Month(DateAdd("m", x, Beginning))
  
  DisplayMonth = Mmonth & "/" & "1/" & Year(DateAdd("m", x, Beginning))
  
  DaysByMonth = Mmonth & "/" & "1/" & Year(Date) & ": " & Day(DateSerial(Year(DisplayMonth), Month(DisplayMonth) + 1, 0))
  
   
  PrintMonth = Format(DisplayMonth, "mmm-yy")
 
 
 If Month(Beginning) = Month(DisplayMonth) Then
  
   FinalResults = PrintMonth & "  " & (Day(DateSerial(Year(DisplayMonth), Month(DisplayMonth) + 1, 0))) - Day(Beginning) + 1
  
 ElseIf Month(Ending) = Month(DisplayMonth) Then
  
  FinalResults = FinalResults & "   " & PrintMonth & "  " & Day(Ending)
  
 Else
     
  FinalResults = FinalResults & "   " & PrintMonth & "  " & Day(DateSerial(Year(DisplayMonth), Month(DisplayMonth) + 1, 0))
  
  End If
 
 Next x
  
 Me.TargetField = FinalResults

 End Sub

StartDate and EndDate have to be declared as DateTime Datatypes.

This code was developed/tested on a machine using the American date format of 'mm-dd-yyyy.' Since you are using a non-US date format, you might want read Allen Browne's article on the subject, since it can cause problems in Access:

http://allenbrowne.com/ser-36.html

Somebody sharper than myself will probably come along with a hack that will do this in 8 lines, but the above works.

Linq ;0)>