Sorting formatted date field "YYYY/'WW" in a cross tab - access 2007

[merritth]

Hi - I'm trying to create a crosstab using week number and year - WeekNum: Format([ds_ship_date],"yyyy/ww"), everything appears to work but in the cross tab it sorts as 2020/1, 2020/10,2020/2..

Please help how I can make it work!

[cactusdata]

You can use Format twice:

WeekNum: Format([ds_ship_date], "yyyy\/") & Format(DatePart("ww", [ds_ship_date]), "00")

[merritth]

Works perfectly! Thanks a ton that really helped.