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:
Code:
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)>
Last edited by Missinglinq; 02-12-2013 at 11:06 PM.
The problem with making anything foolproof...is that fools are so darn ingenious!
All posts/responses based on Access 2003/2007