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

1. 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. 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.

3. 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. 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
•