i want to sort my data according to weeks in ascending order. problem is because i use the Format$ function to get display in weeks from the date, ie WW: Format$([date],"yyyy-ww"), the sorting gives me WW= 2001-1 first followed by 2001-10, then 2001-11,2001-12 and then 2001-2,2001-21,2001-22.basically, the system looks at the first digit and sorts accordingly but what i need is in the order of 1,2,3,4,5......,10,11,etc.
hope this explanation of my problem is clear. any ideas on how to deal with this?

If you still have the original date then sort on the date otherwise create a new field where you strip out the week value, convert it to number and sort on that

[This message has been edited by Harry (edited 04-26-2002).]

Or if you wanna do it a little bit less easier than Haary's solution, use this:

IIF(Len(Format$([Date],"ww"))=1,
Format$([Date],"yyyy-"&"0"&"ww"),
Format$([Date],"yyyy-ww"))

RV

Here's the formula


WeekNo: Val(Right([WeekV],Len([WeekV])-InStr([WeekV],"-")))

WeekV is your week value ie 2002-3

HTH

Thank you so much!
it works perfectly! http://www.access-programmers.co.uk/ubb/smile.gif