Display week number in each of month

Topic

New Reply

Hi,

I would like to create a combo box shows number of the week for each month. The format should be “Feb, Week 1(2/2/09 – 2/8/09); Feb, Week 2 (2/9/09 – 2/15/09).”

I was able to build a query displaying the format that I want, but I don’t know how to show the week number within that month. For example, Jan 09 has 5 weeks and I want to show Jan, Week 1 – 5. Then on Feb 09, start with Week 1 again.

Please find attached database with qryAllDates and the field is MinMaxDate.

Thanks in advance.

Reply | Quote

Replies

You’ll find a custom VBA function to retrieve the week of the month in [post=”340905″]Post 340905[/post].

Reply | Quote

You’ll find a custom VBA function to retrieve the week of the month in [post=”340905″]Post 340905[/post].

Thanks, Hans.

Which Combo Box event that I should input the codes below:

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

Thanks.

Reply | Quote

Combo box? I didn’t see any combo box in your database…

Reply | Quote

Combo box? I didn’t see any combo box in your database…

Sorry, Hans. I am confused. I thought I only can use this codes in the combo box. Can I put the codes on the query that I built? Or is the best way to put it in a new comob box?

Thanks.

Reply | Quote

You can use custom functions in queries too.

Reply | Quote

You can use custom functions in queries too.

Can you provide more details of how to use this custom functions in my queries?

Thanks

Reply | Quote

Copy the GetWeekOfMonth function into a standard module. You can create a calculated field like this:

TheWeek: GetWeekOfMonth([Alldates])

It can also be done without VBA, however:

MinMaxDate: MonthName(Month([Alldates]),True) & “, ” & “Week” & ” ” & (Day([AllDates])-1)7+1 & ” (” & ([AllDates]-(Day([AllDates])-1) Mod 7) & ” – ” & ([AllDates]-(Day([AllDates])-1) Mod 7+6) & “)”

See the attached version.

Reply | Quote