sort week number in ascending order

[tekila]

I have a chart whereby the bar graphs are tabulated by week number, so in the rowsource of the chart, the date field is formatted as "ww". However for a range of say week 9 to 11, the bar graphs are tabulated in the order of 10, 11, 9. How can I make 9 to become 09 instead?

 

[MichaelRed]

? Right("00" & 9, 2)




MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[tekila]

Where do I place this code?

 

[tekila]

Just realized the months are also giving me the same problem. Eg. Apr;Mar instead of Mar;Apr.

 

[ccooper2]

To get around the month issue, I created a table with the month name and number of the month. I then included the number of the month in the query and sorted it but hid it from the report. That should list the months in order, not alphabetically.

 

[MichaelRed]

? Month(Date)
4
? Date
4/09/2002

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[tekila]

I then included the number of the month in the query and sorted it but hid it from the report.

candia, I can't figure this out, please explain.

MichaelRed, can you please elaborate, especially Right("00" & 9, 2)?

MY SQL statements for monthly chart:

SELECT Format([FCT Yield Chart]![Date],"mmm&quot AS Month, Sum([FCT Yield Chart]![PASS]) AS Pass, Sum([FCT Yield Chart]![PASS])/(Sum([FCT Yield Chart]![PASS])+Sum([FCT Yield Chart]![FAIL(Debug)])) AS Yield, Sum([FCT Yield Chart]![PASS])+Sum([FCT Yield Chart]![FAIL(Debug)]) AS Total
FROM [FCT Yield Chart]
GROUP BY Format([FCT Yield Chart]![Date],"mmm&quot
ORDER BY Format([FCT Yield Chart]![Date],"mmm"

For weekly chart, it'll be "ww" instead of "mmm".

 

[MichaelRed]

"00" & 9 => "009"
Right ("009", 2) => "09"
MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[tekila]

Bingo! I managed to solve it.

Here's the SQL for the monthly chart:

SELECT (Format([FCT Yield Chart]![Date],"mmm"" '""yy&quot) AS Month, Sum([FCT Yield Chart]![PASS]) AS Pass, Sum([FCT Yield Chart]![PASS])/(Sum([FCT Yield Chart]![PASS])+Sum([FCT Yield Chart]![FAIL(Debug)])) AS Yield, Sum([FCT Yield Chart]![PASS])+Sum([FCT Yield Chart]![FAIL(Debug)]) AS Total
FROM [FCT Yield Chart]
GROUP BY (Format([FCT Yield Chart]![Date],"mmm"" '""yy&quot), (Year([FCT Yield Chart]![Date])*12+Month([FCT Yield Chart]![Date])-1)
ORDER BY (Year([FCT Yield Chart]![Date])*12+Month([FCT Yield Chart]![Date])-1)
, (Format([FCT Yield Chart]![Date],"mmm"" '""yy&quot);

Here's the code for the weekly chart:

SELECT "W" & Format([FCT Yield Chart]![Date],"ww&quot AS Week, Sum([FCT Yield Chart]![PASS]) AS Pass, Sum([FCT Yield Chart]![PASS])/(Sum([FCT Yield Chart]![PASS])+Sum([FCT Yield Chart]![FAIL(Debug)])) AS Yield, Sum([FCT Yield Chart]![PASS])+Sum([FCT Yield Chart]![FAIL(Debug)]) AS Total
FROM [FCT Yield Chart]
GROUP BY "W" & Format([FCT Yield Chart]![Date],"ww&quot, (Year([FCT Yield Chart]![Date])*CLng(54)+DatePart("ww",[FCT Yield Chart]![Date],0)-1)
ORDER BY (Year([FCT Yield Chart]![Date])*CLng(54)+DatePart("ww",[FCT Yield Chart]![Date],0)-1)
, "W" & Format([FCT Yield Chart]![Date],"ww"

Nevertheless, thanks to those who've offered to help.