MS Access Weekly query

I'm trying to create a weekly query. from Mon to Sun.
I tried format ([time],"ww") but can't understand how it works.
Hiroyuki TamuraField EngineerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PatHartmanCommented:
I'm not sure where you are going with this so I just made a sample from one of my tables.

SELECT Year([LastChangeDT]) AS ChngYear, Format([LastChangeDT],"ww") AS ChngWeek, Count(*) AS Expr1
FROM tblChangeDT
GROUP BY Year([LastChangeDT]), Format([LastChangeDT],"ww");

This query returns:
CountByWeek.JPG
Hiroyuki TamuraField EngineerAuthor Commented:
Thank you. How does "ww" define a week?
Gustav BrockCIOCommented:
Access will not return correct ISO weeknumbering and weeks cross calendar years, so you will need a custom function like this:

Public Function ISO_WeekYearNumber( _
  ByVal datDate As Date, _
  Optional ByRef intYear As Integer, _
  Optional ByRef bytWeek As Byte) _
  As String

' Calculates and returns year and week number for date datDate according to the ISO 8601:1988 standard.
' Optionally returns numeric year and week.
' 1998-2007, Gustav Brock, Cactus Data ApS, CPH.
' May be freely used and distributed.

  Const cbytFirstWeekOfAnyYear  As Byte = 1
  Const cbytLastWeekOfLeapYear  As Byte = 53
  Const cbytMonthJanuary        As Byte = 1
  Const cbytMonthDecember       As Byte = 12
  Const cstrSeparatorYearWeek   As String = "W"
  
  Dim bytMonth                  As Byte
  Dim bytISOThursday            As Byte
  Dim datLastDayOfYear          As Date

  intYear = Year(datDate)
  bytMonth = Month(datDate)
  bytWeek = DatePart("ww", datDate, vbMonday, vbFirstFourDays)
  
  If bytWeek = cbytLastWeekOfLeapYear Then
    bytISOThursday = Weekday(vbThursday, vbMonday)
    datLastDayOfYear = DateSerial(intYear, cbytMonthDecember, 31)
    If Weekday(datLastDayOfYear, vbMonday) >= bytISOThursday Then
      ' OK, week count of 53 is caused by leap year.
    Else
      ' Correct for Access97/2000+ bug.
      bytWeek = cbytFirstWeekOfAnyYear
    End If
  End If

  ' Adjust year where week number belongs to next or previous year.
  If bytMonth = cbytMonthJanuary Then
    If bytWeek >= cbytLastWeekOfLeapYear - 1 Then
      ' This is an early date of January belonging to the last week of the previous year.
      intYear = intYear - 1
    End If
  ElseIf bytMonth = cbytMonthDecember Then
    If bytWeek = cbytFirstWeekOfAnyYear Then
      ' This is a late date of December belonging to the first week of the next year.
      intYear = intYear + 1
    End If
  End If
  
  ISO_WeekYearNumber = CStr(intYear) & cstrSeparatorYearWeek & Format(bytWeek, "00")

End Function

Open in new window

Then, in your query, group by week-year:

Select Sum([SomeField])
From YourTable
Group By  ISO_WeekYearNumber([YourDateField])

Open in new window

/gustav

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ensure Business Longevity with As-A-Service

Using the as-a-service approach for your business model allows you to grow your revenue stream with new practice areas, without forcing you to part ways with existing clients just because they don’t fit the mold of your new service offerings.

PatHartmanCommented:
You should look up the syntax for Format() and that will tell you.  Or, you can start typing in a VBA code module or in the immediate window and Intellisense will help you.  the third argument below specifies which weekday is the first day of a week.  The fourth argument specifies how you want to handle the first week of the year.  I chose the Jan1 option which says that regardless of what day of the week Jan1 falls on, call that week 1.  So if Jan1 is a Sunday, week 1 will be just that date.

Format(Date(),"ww",vbMonday,vbFirstJan1)  '''That configuration makes today Week 14
Format(Date(),"ww",vbMonday,vbFirstFourDays)   ''That configuration makes today Week 13
PatHartmanCommented:
Gus' function returns 2017W13 for today's date.
Gustav BrockCIOCommented:
Problem is, that Format and DatePart don't always get week 53 right. Also, both week 1 and 52/53 can belong to either a calendar year before or after New Year. Thus, you need that year part as well to sort and group correctly when data spans calendar years.

/gustav
PatHartmanCommented:
That's why I included Year in my query example.  Since year does not divide cleanly into weeks, it is necessary to decide how to handle the first week of the year when it doesn't start on the first day of the week and that also impacts the definition of the Last week of the year.  The ISO method may be what Tamura wants but in this case, the correct answer is actually what satisfies the business users.
Gustav BrockCIOCommented:
Who knows? Questioneer is  silent.

/gustav
Hiroyuki TamuraField EngineerAuthor Commented:
Thank you, all. I'm trying to add ISO method.
is this the right way?
Snap1972.png
Gustav BrockCIOCommented:
Yes.
Hiroyuki TamuraField EngineerAuthor Commented:
Thank you all
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.