Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Portland, Maine, USA
    Posts
    173
    Thanks
    0
    Thanked 0 Times in 0 Posts

    How many times selected weekday appears in month (XP/SP-2)

    I'm using the following code to fill a combobox with First, Second, ect.... that the selected weekday appears in the currently
    selected month. I thought this would do it but the case statement is returning 0. Can anyone tell me what I'm failing to do here?

    <pre>Select Case DateDiff("w", Month(frm!txtTripDate) & "/1/" _
    & Year(frm!txtTripDate), frm!txtTripDate)
    Case 1 'First Week of Month
    Me.cboMonthlyWeekInterval = "First"
    Me.cboYearlyWeekInterval = "First"
    Case 2 'Second week of Month
    Me.cboMonthlyWeekInterval = "Second"
    Me.cboYearlyWeekInterval = "Second"
    Case 3 'Third Week of Month
    Me.cboMonthlyWeekInterval = "Third"
    Me.cboYearlyWeekInterval = "Third"
    Case 4 'Fourth week of Month
    Me.cboMonthlyWeekInterval = "Fourth"
    Me.cboYearlyWeekInterval = "Fourth"
    Case 5
    Me.cboMonthlyWeekInterval = "Last"
    Me.cboYearlyWeekInterval = "Last"
    End Select</pre>



    Thanks for any help with this.
    Don

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: How many times selected weekday appears in month (XP/SP-2)

    Assuming that frm!txtTripDate is really text, you can't do a date this way unless you use a CDate function with it. DateDiff requires that the arguments be formatted as Access dates, which is a floating point number that the integer portion represents the number of days since 12/31/1899, and the fraction portion represents the portion of a day. You could try putting the number sign (#) around your dates and see if Access will implicitly do the conversion, but to be safe should really convert to a date format. If frm!txtTripDate does really contain an Access date, then you need to convert the first date in the expression to a date.

    It appears you are trying to calculate the week of the month with your expression, but most companies use some sort of fiscal calendar that arbitrarily defines weeks of the month. The most common is 4 weeks for Jan, Feb, Apr, May, Jul, Aug, Oct, Nov, and 5 for Mar, Jun, Sep and either 5 or 6 for Dec. Your calculation won't really do that sort of thing if that is what you need to do.
    Wendell

  3. #3
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: How many times selected weekday appears in month (XP/SP-2)

    If I understand what you are trying to do correctly, here are some user-defined functions that may or may not be useful. This function determines nth occurence of day of week in month for given date:

    Public Function GetWeekOfMonth(dtDate As Date) As Integer
    GetWeekOfMonth = IIf(Day(dtDate) Mod 7 = 0, Day(dtDate) 7, Day(dtDate) 7 + 1)
    End Function

    Determine how many days in a given month:

    Public Function GetDaysInMonth(dtDate As Date) As Integer
    GetDaysInMonth = Day(DateSerial(Year(dtDate), Month(dtDate) + 1, 0))
    End Function

    Determine how many times the day of week for given date occurs in that month:

    Public Function GetWeekDaysInMonth(dtDate As Date) As Integer

    Dim dtEndDate As Date
    Dim intResult As Integer
    ' Determine last day of month:
    dtEndDate = DateSerial(Year(dtDate), Month(dtDate) + 1, 0)
    intResult = (Weekday(dtEndDate) - Weekday(dtDate) + 7) Mod 7

    If intResult < Day(dtEndDate) Mod 28 Then
    GetWeekDaysInMonth = 5
    Else
    GetWeekDaysInMonth = 4
    End If
    End Function

    Public Function GetWeekInterval(dtDate As Date) As String

    Dim intWeeks As Integer
    intWeeks = GetWeekDaysInMonth(dtDate)

    Select Case GetWeekOfMonth(dtDate)
    Case 1
    GetWeekInterval = "First"
    Case 2
    GetWeekInterval = "Second"
    Case 3
    GetWeekInterval = "Third"
    Case 4
    GetWeekInterval = IIf(intWeeks = 4, "Last", "Fourth")
    Case 5
    GetWeekInterval = IIf(intWeeks = 5, "Last", "Fifth")
    End Select

    End Function

    As Wendell noted, how your organization defines a "week" may affect how you determine the "interval". If a week is defined as starting on Sunday (default in VBA), and the interval is supposed to reflect which week in month the date falls on (as opposed to nth occurrence of day of week), this function may return desired results:

    Public Function GetWeekIntervalRev(dtDate As Date) As String

    Select Case DateDiff("ww", DateSerial(Year(<!t>[dtDate]<!/t>), Month(<!t>[dtDate]<!/t>), 1), <!t>[dtDate]<!/t>)
    Case 0
    GetWeekIntervalRev = "First"
    Case 1
    GetWeekIntervalRev = "Second"
    Case 2
    GetWeekIntervalRev = "Third"
    Case 3
    GetWeekIntervalRev = "Fourth"
    Case 4
    GetWeekIntervalRev = "Last"
    End Select

    End Function

    If "week" defined otherwise, function would have to be modified. Note: all functions shown above require a valid date be provided to function to return correct results. If date represented as text, use a VBA conversion function such as CDate to ensure valid date is passed to function.

    HTH

  4. #4
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Portland, Maine, USA
    Posts
    173
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How many times selected weekday appears in month (XP/SP-2)

    Thanks guys for taking the time to help!!

    The line of code: Select Case DateDiff("ww", DateSerial(Year([dtDate]), Month([dtDate]), 1), [dtDate])
    was exactly what I needed. Thanks Mark!

    Don

Posting Permissions

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