date order

[orshims]

hi guys
i have a query which puts a date field in order i use format([date], "ww mm"] this in my query to give me the date in order by weekthis works but now ive got week 10 in the query this goes to the front of the list
which is correct because it recognizes the 1 in the ten to come first
but how do i do it so the weeks count up in line???????
eg
1,2,3,4,etc and not 10,1,2,3,4,5, etc

thanks guys

 

[SOS]

I just tried with my date field and it worked fine.
EDIT - no it didn't really. I forgot to set the sort. So, also remember that when you use FORMAT it changes it to TEXT so your numbers need to have leading zeroes to sort properly.

Is your DATE field really set to be date in the table or text? Also, DATE should not be used as a field or object name because it is an Access Reserved Word.

 

[gemma-the-husky]

create two calc fields

one for week no,
one for month no

sort on both these columns

then it will work fine

 

[SOS]

create two calc fields

one for week no,
one for month no

sort on both these columns

then it will work fine

Good answer!

 

[gemma-the-husky]

thinking again - you may actually NEED to see the week/month numbers

but surely just sorting by date will work as well?