Results 1 to 11 of 11
  1. #1
    New Lounger
    Join Date
    Sep 2002
    Location
    St. Paul, Minnesota, USA
    Posts
    18
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Counting [mon]days between two dates (2000)

    Is there a way to count the number of times a day of the week occurs between two dates?
    I have a need to count the number of newspapers schools that receive funding get in a school year. Each day of the week, a school can get a different number of newspapers (and most of them don't get newspapers on Saturday or Sunday)

    So, I have the date they start getting newspapers, and the date they stop getting newspapers, and how many newspapers they receive each day of the week. I want to, for instance count up the number of Mondays between the two dates, and then multiply that by the Monday number. (Then, of course, add all the numbers up for a grand total, but that's easy.)

    The two problems that I have found so far are that I can count the number of days between two dates, but it includes Saturdays and Sundays, and also that different days can have different quantities, so a complete count isn't really all that helpful in every case.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Counting [mon]days between two dates (2000)

    Here is a VBA function to help you. You can copy it into a standard module, then call it in other code or in a query.

    ' Arguments:
    ' BegDate: beginning date
    ' EndDate: ending date
    ' aDay: day of the week; 1 = Sunday, 2 = Monday, ...
    ' In code, use vbSunday, vbMonday, ...
    ' Example:
    ' CountDaysBetween(#3/3/2003#,#3/12/2003#,2) counts the number of Mondays
    ' in the period from March 3 through March 12, 2003. Returns 2.

    Function CountDaysBetween(BegDate As Date, EndDate As Date, aDay As Integer) As Integer
    Dim d As Integer
    If BegDate > EndDate Then Exit Function
    If aDay < 1 Or aDay > 7 Then Exit Function
    d = (EndDate - BegDate) \ 7
    If WeekDay(EndDate, aDay) < WeekDay(BegDate, aDay) Or WeekDay(BegDate, aDay) = 1 Then
    d = d + 1
    End If
    CountDaysBetween = d
    End Function

  3. #3
    New Lounger
    Join Date
    Sep 2002
    Location
    St. Paul, Minnesota, USA
    Posts
    18
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Counting [mon]days between two dates (2000)

    Excellent!

    So, I could then use calculated fields in the query to do the count for each day, by calling the module in the query for each day of the week, correct?

    If this is the case, then would it be best to somehow define the number for each day of the week at the field level, or would I want to create seven modules, one for each day of the week, and the day hardcoded into the mdule?
    And if the former, how would I do the definition?

  4. #4
    New Lounger
    Join Date
    Sep 2002
    Location
    St. Paul, Minnesota, USA
    Posts
    18
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Counting [mon]days between two dates (2000)

    Also, is the EndDate inclusive?

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Counting [mon]days between two dates (2000)

    You don't need seven modules! The function has an argument for the day of the week, so that you can call it to calculate the number of Mondays, or Wednesdays, or Saturdays...

    Let's say that your table contains a field FirstDate and a field LastDate; then you can calculate the number of Mondays, Tuesdays etc. within the period from FirstDate and LastDate (inclusive) in one query; you can multiply these by the number of newspapers per weekday in the same query, or in another one.

    The calculated fields for Sundays, Mondays, Tuesdays etc. would look like:

    NumOfSundays:CountDaysBetween([FirstDate],[LastDate],1)
    NumOfMondays:CountDaysBetween([FirstDate],[LastDate],2)
    NumOfTuesdays:CountDaysBetween([FirstDate].[LastDate],3)
    ...
    NumOfSaturdays:CountDaysBetween([FirstDate],[LastDate],7)

  6. #6
    New Lounger
    Join Date
    Sep 2002
    Location
    St. Paul, Minnesota, USA
    Posts
    18
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Counting [mon]days between two dates (2000)

    I had figured that would be the case, this example is wonderful - while I seem to be good at coming up with things to do with databases, I am not always so clever at making them do it.

    At the moment, Hans, you're my hero, and should you ever make it to Minnesota, I definitely owe you a drink or dinner or something.

  7. #7
    2 Star Lounger
    Join Date
    Mar 2004
    Location
    Mechanicsville, Virginia, USA
    Posts
    130
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Counting [mon]days between two dates (2000)

    Hans,

    I am taking this Function to use in a database. One question on the query used with this function.

    NumOfSundays:CountDaysBetween([FirstDate],[LastDate],1)

    If FirstDate is a Sunday, I need for it to add 1 to it. How can I modify this query to add this extra piece?

    Thanks,

    Marie

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Counting [mon]days between two dates (2000)

    As it is now, CountDaysBetween(#08/22/04#, #08/29/04#,1) returns 2, since both the first and the last date are Sundays. Do you really want to return 3? <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

  9. #9
    2 Star Lounger
    Join Date
    Mar 2004
    Location
    Mechanicsville, Virginia, USA
    Posts
    130
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Counting [mon]days between two dates (2000)

    Unbelievable isn't it. <img src=/S/crazy.gif border=0 alt=crazy width=15 height=15> But I think that I would like for it to count 3 as in your example <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15>

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Counting [mon]days between two dates (2000)

    You can add an extra test to the function (indicated in bold):

    Function CountDaysBetween(BegDate As Date, EndDate As Date, aDay As Integer) As Integer
    Dim d As Integer
    If BegDate > EndDate Then Exit Function
    If aDay < 1 Or aDay > 7 Then Exit Function
    d = (EndDate - BegDate) \ 7
    If WeekDay(EndDate, aDay) < WeekDay(BegDate, aDay) Or WeekDay(BegDate, aDay) = 1 Then
    d = d + 1
    End If
    If WeekDay(BegDate) = 1 Then
    d = d + 1
    End If
    CountDaysBetween = d
    End Function

  11. #11
    2 Star Lounger
    Join Date
    Mar 2004
    Location
    Mechanicsville, Virginia, USA
    Posts
    130
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Counting [mon]days between two dates (2000)

    Hans,

    You were right....you always are. <img src=/S/wink.gif border=0 alt=wink width=15 height=15> I needed the function to subtract 1 for "firstdate" = 1. I made the change and it works beautifully.

    On a side note, I have picked up my "Beginning Access 2002 VBA" book. Wish me luck

    Thanks again!! <img src=/S/kiss.gif border=0 alt=kiss width=34 height=15>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •