Sorting by week

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I want to create a graph where the data is displayed in ascending order by
week. My data has a date field and Access uses Format([Myfield],"ww",0) to
summarise by week. However, my data spans more than one year so the first
week should be 01 2003 then 02 2003 so I've used Format([Myfield],"ww yyyy")
but Access sorts it thus 01 2003, 01 2004, 10 2003 10 2004.

How can I get the data sorted correctly. Also what does the 0 do in the
Format function?

Thanks
 
Have you considered
Format([MyField],"yyyy ww")
I forget what the 0 is for. I would need to look this up in Help. You might
want to do this.
 
Duane

Yes tried that but it still does not sort correctly. I think I'll have to
add two other fields that are not displayed i.e. Year and Week, and sort by
them but it doesn't seem like a very elegant solution.
 
You may want to suggest what you mean by "still does not sort correctly".
Formatting your date with "ww" will not work correctly since 9 will sort
after 10. You would need to use something like
Year([OrderDate]) & Format(DatePart("ww",[OrderDate]),"00")

--
Duane Hookom
MS Access MVP


Snowsride said:
Duane

Yes tried that but it still does not sort correctly. I think I'll have to
add two other fields that are not displayed i.e. Year and Week, and sort by
them but it doesn't seem like a very elegant solution.

Snowsride said:
I want to create a graph where the data is displayed in ascending order by
week. My data has a date field and Access uses Format([Myfield],"ww",0) to
summarise by week. However, my data spans more than one year so the first
week should be 01 2003 then 02 2003 so I've used Format([Myfield],"ww yyyy")
but Access sorts it thus 01 2003, 01 2004, 10 2003 10 2004.

How can I get the data sorted correctly. Also what does the 0 do in the
Format function?

Thanks
Click to expand...
Click to expand...
 
Back
Top