Date to Month, Week format

[r0nniem]

Hi,

I'm wanting to format a date to month then week ... eg

June 06 Week 1 would cover date range thursday 01/06/06 to Saturday 03/06/06
June 06 Week 2 would cover date range Sunday 04/06/06 to Saturday 10/06/06

I would appreaciate your help thanks

 

[rubbernilly]

Look at using the DatePart() function. I would create a wrapper function, something like this:

Public Function CMonthWeek(dDate as Date) as String

Select Case Month(dDate)
  Case 1
    CMonthWeek = "January " & Year(dDate) & " "
  Case 2
    CMonthWeek = "February " & Year(dDate) & " "
  Case 3
    CMonthWeek = "March " & Year(dDate) & " "
  Case 4
    CMonthWeek = "April " & Year(dDate) & " "
  Case 5
    CMonthWeek = "May " & Year(dDate) & " "
  Case 6
    CMonthWeek = "June " & Year(dDate) & " "
  Case 7
    CMonthWeek = "July " & Year(dDate) & " "
  Case 8
    CMonthWeek = "August " & Year(dDate) & " "
  Case 9
    CMonthWeek = "September " & Year(dDate) & " "
  Case 10
    CMonthWeek = "October " & Year(dDate) & " "
  Case 11
    CMonthWeek = "November " & Year(dDate) & " "
  Case 12
    CMonthWeek = "December " & Year(dDate) & " "
End Select

CMonthWeek = CMonthWeek & (DatePart("ww", dDate) - DatePart("ww", CDate("1/" & Month(dDate) & "/" & Year(dDate)))

End Function

Then, in your query, define your field as:

YourFieldName: CMonthDate([YourDateField])

HTH

 

[Eupher]

Here's another approach with a wrapper function:

Public Function FormatMonthWeek(dtDate As Date) As String
Dim FirstDay As Date
Dim PrevSunday As Date
Dim x As Integer

FirstDay = DateSerial(Year(dtDate), Month(dtDate), 1)
PrevSunday = (FirstDay - Weekday(FirstDay)) + 1
x = 0
Do Until PrevSunday > dtDate
    PrevSunday = PrevSunday + 7
    x = x + 1
Loop

FormatMonthWeek = Format(dtDate, "mmmm yyyy") & " Week " & x

End Function

A function is not an absolute necessity, the operation may be performed in a single (somewhat complex) expression (minor variation to rubbernilly's excellent suggestion above:

Format(dDate, "mmmm yyyy") & " Week " & ((DatePart("ww", dDate) + 1) - DatePart("ww", DateSerial(Year(dDate), Month(dDate), 1)))

HTH,

Ken S.

 

[r0nniem]

Thank you both so much for your assistance ... I'll let you know how I go.

Ronnie

 

[r0nniem]

The requirements have now changed ...

I need it to be in the following format

June Week 1 Monday 29 May to Friday 2 June
June Week 2 Monday 5 June to Friday 9 June
June Week 3 Monday 12 June to Friday 16 June
June Week 4 Monday 19 June to Friday 23 June
June Week 5 Monday 26 June to Friday 30 June
July Week 1 Monday 3 July to Friday 7 July

If you could help out with another function, that would be faboulous!