Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Jan 2005
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Week Number and Week Date (Access XP)

    Hello everyone! Attached you will see VB code that I'm using to run a report monthly and it returns
    ex: Nov 04, Dec 04, Jan 05, and Feb 05 for the activity date field.

    I'm also trying to change the VB code below to extract the week number and also in turn extract the date of the first day of the week.
    ex: Week# 46 Date 11/07/04, Week# 47 Date 11/14/04, Week# 48 Date 11/21/04 and Week# 49 11/28/04 for the activity date field.

    Any ideas? Please see attached.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Week Number and Week Date (Access XP)

    To get the week number of Q.[activitydate], use <code>Format(Q.[activitydate], "ww")</code>
    To get the first day in the week of Q.[activitydate], use <code>Q.[activitydate]-Weekday(Q.[activitydate])+1</code>
    Post back if necessary.

  3. #3
    New Lounger
    Join Date
    Jan 2005
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Week Number and Week Date (Access XP)

    Hi Hans,

    Thank you for helping me. When I run the VB code I get the week number which is exactly what I wanted, but it's only for that one week. How can I increment the week number by one. For example Week# 46 then I would like to see Week# 47, 48 ,and 49 for that month.

    What you will see in my results below is the first week # 46, but then you'll see Month00 there after.

    Result below
    ? mysql
    INSERT INTO TtblFuncationAreas (SortOrder, Type, Support, FunctionalArea, SIDepartment, Mon1, Mon2, Mon3, Mon4, Mon5) SELECT A.SortOrder, A.Type, nz(C.[SupportArea], "All") AS Support, A.FunctionalArea, A.SIDepartment, sum(nz(C.Mon1,0)) AS Mon1, sum(nz(C.Mon2,0)) AS Mon2, sum(nz(C.Mon3,0)) AS Mon3, sum(nz(C.Mon4, 0)) As Mon4, sum(nz(C.Mon5, 0)) As Mon5 FROM qryAllAreas AS A LEFT JOIN (SELECT Q.SupportArea, Q.SIDepartment, Q.Area, sum(IIF(Format(Q.[activitydate],"ww") = "46", Q.ActualEffortHours, 0)) as Mon1, sum(IIF(Format(Q.[activitydate],"ww") = "Mar00", Q.ActualEffortHours, 0)) as Mon2, sum(IIF(Format(Q.[activitydate],"ww") = "Apr00", Q.ActualEffortHours, 0)) as Mon3, sum(IIf(Format(Q.[ActivityDate],"ww") = "May00", Q.ActualEffortHours, 0)) as Mon4, 0 As Mon5 FROM qryCombinedAreas as Q WHERE Q.SupportArea Like "*" AND Q.SIDepartment Is Not Null AND Q.Area Is Not Null GROUP BY Q.SupportArea, Q.SIDepartment, Q.Area) AS C ON A.SIDepartment = C.SIDepartment GROUP BY A.SortOrder, A.Type, nz(C.[SupportA
    rea], "All"), A.FunctionalArea, A.SIDepartment

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Week Number and Week Date (Access XP)

    You can add 1, 2 and 3 to the first week number, or add 7, 14 and 21 to the date, then calculate the week numbers, or use DateAdd("ww", n, Q.[activitydate]) where n = 1, 2, 3, then calculate the week numbers.

    Note: don't forget to replace the mmmyy dates such as "May00" with week numbers.

Posting Permissions

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