first Monday date in Quarter

[hughess7]

Hi

I have quater start dates by the user choosing which Year and which Month
(Jan, Apr, Jul, Oct) and storing in a field as follows:

Date1 = DateSerial(YearChoice, MonthChoice, 1)

I need to plot this in a crosstab though and therefore I need the StartDate
to be a Monday's date. 01/04/07 was a Sunday so if left like this it does not
put my data in the correct columns on my report.

Searching the forum but so far not found an answer - can anyone help?

Thanks in advance for any help.
Sue

 

[hughess7]

Hi, got a little closer...

This works out previous Monday
Me!StartDate = DateAdd("d", 1 - Weekday(Date1, 2), Date1)

but if the 01 of month falls on a Sat or Sun then I want to display the
Monday after Date1

 

[hughess7]

Solved . Not sure it is the most efficient/best way of doing this though?

Date1 = DateSerial(YearChoice, MonthChoice, 1)

Select Case Weekday(Date1)
Case 7
Date1 = Date1 + 2
Case 1
Date1 = Date1 + 1
Case Else
Date1 = DateAdd("d", 1 - Weekday(Date1, 2), Date1)
End Select

 

[Ken Snell \(MVP\)]

Perhaps this function will work for you:

Public Function DateForFixedDayOfMonthYear(Day_Year As Integer, _
Day_Month As Integer, Day_Day As Integer, Day_WeekNum As Integer) As
Date
' Day_Year is the actual year for the desired date
' Day_Month is the actual month for the desired date
' Day_Day is the number of the weekday (1 = Sunday, etc. to 7 = Saturday)
' Day_WeekNum is the ordinal value for the desired date (e.g., 1 = first, 2
= second)
'
' So, if you want the 2nd Wednesday of May 2005:
' MyDate = DateForFixedDayOfMonthYear(2005, 5, 4, 2)

DateForFixedDayOfMonthYear = DateSerial(Day_Year, Day_Month, _
8 - DatePart("w", DateSerial(Day_Year, Day_Month, 1), _
1 + Day_Day Mod 7) + (Day_WeekNum - 1) * 7)

End Function