Count the Number of Days per Month in a Date Range

[Pat Ferguson]

Hi, I'm pretty new at Access queries and need a little help. I want to count the numbers of days in a date range by month. That is, date range is 5-Jan-05 to 3-Feb-05 (total DateDiff is 30), but how do I format a expr that would count/show that within the range, January has 27 days and February has 3 days of the total 30 days?
Thank in Advance, Newbie.

[ADezii]

1. How would you expect the results to be displayed, something similar to?
   

   Jan: 27
   Feb: 03

2. This would be difficult to display within a Query, unless it is displayed as a Delimited String such as:
   

   Jan: 27,Feb: 03

3. Kindly be more specific with your Request.

[Pat Ferguson]

ADezil, Option 2 would work best. Thank-yor for your help! I have been trying to get this for 2 weeks! Newbie

[ADezii]

This is probably a much easier, and more efficient, solution to your problem, but at the moment it alludes me. The following Query, using a Calculated Field, will calculate the Total Days by Month for a given [Start] and [End] Date, and return the information in a Comma-Delimited String. It makes 2 Major Assumptions:

1. The [Start] and [End] Fields are of the Date/Time Data type and neither can be NULL.
2. Both [Start] and [End] Dates are within a Year.

1. Query Definition:
   

   SELECT tblTest.Start, tblTest.End, fCalcDaysInRangeByMonth([Start],[End]) AS Totals_By_Month
   FROM tblTest;

2. Function Definition:
   

   Public Function fCalcDaysInRangeByMonth(dteStart As Date, dteEnd As Date) As String
   Dim intNumOfDays As Integer
   Dim intCtr As Integer
   Dim strBuild As String
   Dim strMonth As String
   Dim bytMonth As Byte
   Dim bytTotDaysJan As Byte
   Dim bytTotDaysFeb As Byte
   Dim bytTotDaysMar As Byte
   Dim bytTotDaysApr As Byte
   Dim bytTotDaysMay As Byte
   Dim bytTotDaysJun As Byte
   Dim bytTotDaysJul As Byte
   Dim bytTotDaysAug As Byte
   Dim bytTotDaysSep As Byte
   Dim bytTotDaysOct As Byte
   Dim bytTotDaysNov As Byte
   Dim bytTotDaysDec As Byte
   
   intNumOfDays = DateDiff("d", dteStart, dteEnd)
   
   For intCtr = 0 To intNumOfDays
    bytMonth = Month(DateAdd("d", intCtr, dteStart))
     Select Case bytMonth
       Case 1
         bytTotDaysJan = bytTotDaysJan + 1
       Case 2
         bytTotDaysFeb = bytTotDaysFeb + 1
       Case 3
         bytTotDaysMar = bytTotDaysMar + 1
       Case 4
         bytTotDaysApr = bytTotDaysApr + 1
       Case 5
         bytTotDaysMay = bytTotDaysMay + 1
       Case 6
         bytTotDaysJun = bytTotDaysJun + 1
       Case 7
         bytTotDaysJul = bytTotDaysJul + 1
       Case 8
         bytTotDaysAug = bytTotDaysAug + 1
       Case 9
         bytTotDaysSep = bytTotDaysSep + 1
       Case 10
         bytTotDaysOct = bytTotDaysOct + 1
       Case 11
         bytTotDaysNov = bytTotDaysNov + 1
       Case 12
         bytTotDaysDec = bytTotDaysDec + 1
     End Select
   Next
   
   If bytTotDaysJan > 0 Then strBuild = strBuild & "Jan: " & CStr(bytTotDaysJan) & ","
   If bytTotDaysFeb > 0 Then strBuild = strBuild & "Feb: " & CStr(bytTotDaysFeb) & ","
   If bytTotDaysMar > 0 Then strBuild = strBuild & "Mar: " & CStr(bytTotDaysMar) & ","
   If bytTotDaysApr > 0 Then strBuild = strBuild & "Apr: " & CStr(bytTotDaysApr) & ","
   If bytTotDaysMay > 0 Then strBuild = strBuild & "May: " & CStr(bytTotDaysMay) & ","
   If bytTotDaysJun > 0 Then strBuild = strBuild & "Jun: " & CStr(bytTotDaysJun) & ","
   If bytTotDaysJul > 0 Then strBuild = strBuild & "Jul: " & CStr(bytTotDaysJul) & ","
   If bytTotDaysAug > 0 Then strBuild = strBuild & "Aug: " & CStr(bytTotDaysAug) & ","
   If bytTotDaysSep > 0 Then strBuild = strBuild & "Sep: " & CStr(bytTotDaysSep) & ","
   If bytTotDaysOct > 0 Then strBuild = strBuild & "Oct: " & CStr(bytTotDaysOct) & ","
   If bytTotDaysNov > 0 Then strBuild = strBuild & "Nov: " & CStr(bytTotDaysNov) & ","
   If bytTotDaysDec > 0 Then strBuild = strBuild & "Dec: " & CStr(bytTotDaysDec) & ","
   
   fCalcDaysInRangeByMonth = Left$(strBuild, Len(strBuild) - 1)
   End Function

3. Sample Data:
   

   Start	    End
   1/5/2005	 2/3/2005
   3/21/2011	10/3/2011
   1/3/2008	 12/10/2008

4. Query Results:
   

   Start	    End	         Totals_By_Month
   1/5/2005	  2/3/2005	   Jan: 27,Feb: 3
   3/21/2011	10/3/2011	   Mar: 11,Apr: 30,May: 31,Jun: 30,Jul: 31,Aug: 31,Sep: 30,Oct: 3
   1/3/2008	 12/10/2008	  Jan: 29,Feb: 29,Mar: 31,Apr: 30,May: 31,Jun: 30,Jul: 31,Aug: 31,Sep: 30,Oct: 31,Nov: 30,Dec: 10

[NeoPa]

Try DateDiff().

In SQL :

DateDiff('d', [Start Date], [End Date])

If you want the number of days inclusive (today and tomorrow would result in the value two) then add one to the result.