Sorting by week

[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

 

[Duane Hookom]

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.

 

[Guest]

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.

 

[Duane Hookom]

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

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.

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