By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,316 Members | 2,355 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,316 IT Pros & Developers. It's quick & easy.

Count the Number of Days per Month in a Date Range

P: 2
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.
Nov 5 '11 #1
Share this Question
Share on Google+
4 Replies


ADezii
Expert 5K+
P: 8,679
  1. How would you expect the results to be displayed, something similar to?
    Expand|Select|Wrap|Line Numbers
    1. Jan: 27
    2. Feb: 03
    3.  
  2. This would be difficult to display within a Query, unless it is displayed as a Delimited String such as:
    Expand|Select|Wrap|Line Numbers
    1. Jan: 27,Feb: 03
  3. Kindly be more specific with your Request.
Nov 6 '11 #2

P: 2
ADezil, Option 2 would work best. Thank-yor for your help! I have been trying to get this for 2 weeks! Newbie
Nov 7 '11 #3

ADezii
Expert 5K+
P: 8,679
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:
    Expand|Select|Wrap|Line Numbers
    1. SELECT tblTest.Start, tblTest.End, fCalcDaysInRangeByMonth([Start],[End]) AS Totals_By_Month
    2. FROM tblTest;
    3.  
  2. Function Definition:
    Expand|Select|Wrap|Line Numbers
    1. Public Function fCalcDaysInRangeByMonth(dteStart As Date, dteEnd As Date) As String
    2. Dim intNumOfDays As Integer
    3. Dim intCtr As Integer
    4. Dim strBuild As String
    5. Dim strMonth As String
    6. Dim bytMonth As Byte
    7. Dim bytTotDaysJan As Byte
    8. Dim bytTotDaysFeb As Byte
    9. Dim bytTotDaysMar As Byte
    10. Dim bytTotDaysApr As Byte
    11. Dim bytTotDaysMay As Byte
    12. Dim bytTotDaysJun As Byte
    13. Dim bytTotDaysJul As Byte
    14. Dim bytTotDaysAug As Byte
    15. Dim bytTotDaysSep As Byte
    16. Dim bytTotDaysOct As Byte
    17. Dim bytTotDaysNov As Byte
    18. Dim bytTotDaysDec As Byte
    19.  
    20. intNumOfDays = DateDiff("d", dteStart, dteEnd)
    21.  
    22. For intCtr = 0 To intNumOfDays
    23.  bytMonth = Month(DateAdd("d", intCtr, dteStart))
    24.   Select Case bytMonth
    25.     Case 1
    26.       bytTotDaysJan = bytTotDaysJan + 1
    27.     Case 2
    28.       bytTotDaysFeb = bytTotDaysFeb + 1
    29.     Case 3
    30.       bytTotDaysMar = bytTotDaysMar + 1
    31.     Case 4
    32.       bytTotDaysApr = bytTotDaysApr + 1
    33.     Case 5
    34.       bytTotDaysMay = bytTotDaysMay + 1
    35.     Case 6
    36.       bytTotDaysJun = bytTotDaysJun + 1
    37.     Case 7
    38.       bytTotDaysJul = bytTotDaysJul + 1
    39.     Case 8
    40.       bytTotDaysAug = bytTotDaysAug + 1
    41.     Case 9
    42.       bytTotDaysSep = bytTotDaysSep + 1
    43.     Case 10
    44.       bytTotDaysOct = bytTotDaysOct + 1
    45.     Case 11
    46.       bytTotDaysNov = bytTotDaysNov + 1
    47.     Case 12
    48.       bytTotDaysDec = bytTotDaysDec + 1
    49.   End Select
    50. Next
    51.  
    52. If bytTotDaysJan > 0 Then strBuild = strBuild & "Jan: " & CStr(bytTotDaysJan) & ","
    53. If bytTotDaysFeb > 0 Then strBuild = strBuild & "Feb: " & CStr(bytTotDaysFeb) & ","
    54. If bytTotDaysMar > 0 Then strBuild = strBuild & "Mar: " & CStr(bytTotDaysMar) & ","
    55. If bytTotDaysApr > 0 Then strBuild = strBuild & "Apr: " & CStr(bytTotDaysApr) & ","
    56. If bytTotDaysMay > 0 Then strBuild = strBuild & "May: " & CStr(bytTotDaysMay) & ","
    57. If bytTotDaysJun > 0 Then strBuild = strBuild & "Jun: " & CStr(bytTotDaysJun) & ","
    58. If bytTotDaysJul > 0 Then strBuild = strBuild & "Jul: " & CStr(bytTotDaysJul) & ","
    59. If bytTotDaysAug > 0 Then strBuild = strBuild & "Aug: " & CStr(bytTotDaysAug) & ","
    60. If bytTotDaysSep > 0 Then strBuild = strBuild & "Sep: " & CStr(bytTotDaysSep) & ","
    61. If bytTotDaysOct > 0 Then strBuild = strBuild & "Oct: " & CStr(bytTotDaysOct) & ","
    62. If bytTotDaysNov > 0 Then strBuild = strBuild & "Nov: " & CStr(bytTotDaysNov) & ","
    63. If bytTotDaysDec > 0 Then strBuild = strBuild & "Dec: " & CStr(bytTotDaysDec) & ","
    64.  
    65. fCalcDaysInRangeByMonth = Left$(strBuild, Len(strBuild) - 1)
    66. End Function
    67.  
  3. Sample Data:
    Expand|Select|Wrap|Line Numbers
    1. Start        End
    2. 1/5/2005     2/3/2005
    3. 3/21/2011    10/3/2011
    4. 1/3/2008     12/10/2008
    5.  
  4. Query Results:
    Expand|Select|Wrap|Line Numbers
    1. Start        End             Totals_By_Month
    2. 1/5/2005      2/3/2005       Jan: 27,Feb: 3
    3. 3/21/2011    10/3/2011       Mar: 11,Apr: 30,May: 31,Jun: 30,Jul: 31,Aug: 31,Sep: 30,Oct: 3
    4. 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
    5.  
Nov 7 '11 #4

NeoPa
Expert Mod 15k+
P: 31,707
Try DateDiff().

In SQL :
Expand|Select|Wrap|Line Numbers
  1. 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.
Nov 12 '11 #5

Post your reply

Sign in to post your reply or Sign up for a free account.