How to get the query to sort the week in ascending order 1

[maupiti]

Access 2003

I have a query that when It run, it sort the output by
the week number. 1-1-06 would be week# 1, 1-8-06 would be week# 2, 1-17-06 would be week # 3, and so forth.
The output however was not in order as shown below.
The query treat the Week# as text and not as numeric,
and therefore Week# 11 appear before Week# 5.

Week# A B D
11 $100 $200 $300
5 $110 $210 $320
6 $200 $210 $220
8 $300 $310 $320
9 $400 $410 $420

/////////////////////////////////////////////

TRANSFORM Sum(Table_Graph_Accounting_Other.Total_Cost) AS SumOfTotal_Cost
SELECT FormatNumber(Format([Invoice_Date],"ww"),0) AS Week#
FROM Table_Graph_Accounting_Other
GROUP BY FormatNumber(Format([Invoice_Date],"ww"),0)
ORDER BY FormatNumber(Format([Invoice_Date],"ww"),0)
PIVOT Table_Graph_Accounting_Other.Expense_Category;

 

[Golom]

Instead of

FormatNumber(Format([Invoice_Date],"ww"),0)

try

DatePart("ww",[Invoice_Date])

that returns a numeric value rather than a string.

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[VicRauch]

Wrap your week number in Right("00" & weeknumber, 2) which will always give you a two digit number, which will sort correctly when Access see it as alpha.

[red]Right("00" & [/red]FormatNumber(Format([Invoice_Date],"ww"),0)[red], 2)[/red]

 

[maupiti]

Thank you Golom. You have been a great help!

 

[PHV]

Another (simpler) way:
TRANSFORM Sum(Table_Graph_Accounting_Other.Total_Cost) AS SumOfTotal_Cost
SELECT [!]Int([/!]FormatNumber(Format([Invoice_Date],"ww"),0)[!])[/!] AS Week#
FROM Table_Graph_Accounting_Other
GROUP BY FormatNumber(Format([Invoice_Date],"ww"),0)
ORDER BY [!]1[/!]
PIVOT Table_Graph_Accounting_Other.Expense_Category;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886