Results 1 to 8 of 8
  1. #1
    4 Star Lounger
    Join Date
    Feb 2006
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You'll find a custom VBA function to retrieve the week of the month in Post 340905.

  3. #3
    4 Star Lounger
    Join Date
    Feb 2006
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='761905' date='26-Feb-09 11:21']You'll find a custom VBA function to retrieve the week of the month in Post 340905.[/quote]

    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.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Combo box? I didn't see any combo box in your database...

  5. #5
    4 Star Lounger
    Join Date
    Feb 2006
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='761980' date='26-Feb-09 13:37']Combo box? I didn't see any combo box in your database...[/quote]

    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.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You can use custom functions in queries too.

  7. #7
    4 Star Lounger
    Join Date
    Feb 2006
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='762021' date='26-Feb-09 14:49']You can use custom functions in queries too.[/quote]


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

    Thanks

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    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.
    Attached Files Attached Files

Posting Permissions

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