numerical sort

[oohmygod831]

i have a query that returns data per week number. To obtain the week number i used this : Format([Date],"ww"). This gives me the week numbers great but when the query is sorted the week numbers are not sorted numerically, ie i get this

1
10
11
12
2
3
4
5
6
7
8
9

is there a way to sort my data to be numerical.
Here is the query:

SELECT Format([Date],"ww") AS [Week Number], Sum(Conformance.Scrap_Quantity) AS Scrap, Sum(Conformance.Rework_Quantity) AS Rework
FROM Conformance
WHERE (((Conformance.Procedure_Followed)=False))
GROUP BY Format([Date],"ww");

 

[pbaldy]

The format function converts the value to text. Try wrapping it in CInt().

 

[neileg]

Why not include the original date field and sort on that?

 

[oohmygod831]

i tried including the origional date field but it screws up all the data

 

[boblarson]

If your field is named "DATE" then change it now as it is an Access Reserved word and using it thus will cause you great pain and suffering.

Then, your date field (if it is a date datatype) should sort properly. Right now I think it is gagging on the fact that it is an Access reserved word used incorrectly.

 

[oohmygod831]

cheers will try that

 

[oohmygod831]

Ive changed the Date field to Con_Date and instead of using Format i used DatePart and it works a treat, cheers guys

 

[boblarson]