How to sort a Format ([DateAudited],"yyyy"", week ""ww) field by w

[GillesABelanger]

I need to convert the date using the format in the header, however when I try
to sort the date using the ascending or descending option, it behaves like a
string. Week 11 comes before week 2? Is there a way to sort the formatted
date (converted to week number) in the correct order?

Thanks a lot

 

[Rick Brandt]

I need to convert the date using the format in the header, however when I try
to sort the date using the ascending or descending option, it behaves like a
string. Week 11 comes before week 2? Is there a way to sort the formatted
date (converted to week number) in the correct order?

Include both a formatted date (string) and an unformatted date and sort on the
unformatted one.

 

[GillesABelanger]

Thank you for your prompt answer.This is a query of queries. The first two
queries are totals (crosstabs) queries. I have tried what you are
suggesting, but then I need to select "First" as the total option to order my
data by week. This works for the first level of queries, however, when I do
the query of the two queries, it acts as a filter and I see no more data!
Any other suggestions?

Thanks

 

[GillesABelanger]

Applying that principle (using the date to sort with First as the option in
the total line) to both queries is working just fine. Thansk a lot.