Microsoft Access - sort by year-week (1 Viewer)

Harris@Z

Registered User.
Local time
Today, 03:13
Joined
Oct 28, 2019
Messages
38
Can anyone help please?
I have a group by query and trying to have the result sorted by year-week.
Tried Val(Right(Format$([Order_received],'yyyy/ww'),Len(Format$([Order_received],'yyyy/ww'))-5))

If my query tests for the date range from Nov 2020 till May 2021, if I sort by week only, then last year's weeks appear at the end , i.e., 14, 15, 16, 48, 49, 50

Tried different versions including datepart but not resolving the issue.
Would like it to sort 51, 52, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, etc.

Hope someone can help!
Harris
 

cheekybuddha

AWF VIP
Local time
Today, 01:13
Joined
Jul 21, 2014
Messages
1,012
Include the year in your sort:
ORDER BY Format([Order_received], 'yyyyww')
You can display just the week in the SELECT portion
 

Harris@Z

Registered User.
Local time
Today, 03:13
Joined
Oct 28, 2019
Messages
38
Thanks but that does not work:


Week
202110
202111
202112
202113
202114
20212
20213
20214
20215
20216
20217
20218
20219
 

cheekybuddha

AWF VIP
Local time
Today, 01:13
Joined
Jul 21, 2014
Messages
1,012
OK, then try:
ORDER BY Format([Order_received], 'yyyy') & Right("0" & Format([Order_received], 'ww'), 2)
 

Gasman

Enthusiastic Amateur
Local time
Today, 01:13
Joined
Sep 21, 2011
Messages
10,581
So format the week number? :(

Code:
SELECT AllTransactions.Date, Format([Date],"yyyyww") AS Expr1, Format(Format([Date],"ww"),"00") AS Expr3
FROM AllTransactions
ORDER BY Format(Format([Date],"ww"),"00");
 

Harris@Z

Registered User.
Local time
Today, 03:13
Joined
Oct 28, 2019
Messages
38
So format the week number? :(

Code:
SELECT AllTransactions.Date, Format([Date],"yyyyww") AS Expr1, Format(Format([Date],"ww"),"00") AS Expr3
FROM AllTransactions
ORDER BY Format(Format([Date],"ww"),"00");
Thanks for your input.
Unfortunately partly works. The year is the problem, i.e., last year is at the end
ElisaLCMS_Union ElisaLCMS_Union

WeekWeek
2021202
2021303
2021404
2021505
2021606
2021707
2021808
2021909
20211010
20211111
20211212
20211313
20211414
20204949
20205050
20205151
20205252
 

Gasman

Enthusiastic Amateur
Local time
Today, 01:13
Joined
Sep 21, 2011
Messages
10,581
No :( you combine the year and the format of the week, so you get your leading zero.?

You have a solution now anyway.

Code:
SELECT AllTransactions.Date, Year([Date]) & Format(Format([Date],"ww"),"00") AS Expr1
FROM AllTransactions
ORDER BY Year([Date]) & Format(Format([Date],"ww"),"00") DESC;

1617283459224.png
 

Users who are viewing this thread

Top Bottom