week number sorting

[sdel_nevo]

Hi Guys



i have a query that seperates dates by week number, this works really well.

but when the query runs the results for the week numbers are as follows

Week Number
4
48
5

no matter what sorting i have on the query i can't get the week numbers to go

Week Number
4
5
48

this is the query im using
SELECT Format([unexdata.DueDate],"ww",2) AS [DATE], UNEXProcess.Process, UNEXAreaCapacity.KGPW, Sum(UNEXProcess.QTYOUTST) AS SumOfQTYOUTST, UNEXData.Completed
FROM (UNEXProcess INNER JOIN UNEXAreaCapacity ON UNEXProcess.Process = UNEXAreaCapacity.Area) INNER JOIN UNEXData ON UNEXProcess.ID = UNEXData.ID
GROUP BY Format([unexdata.DueDate],"ww",2), UNEXProcess.Process, UNEXAreaCapacity.KGPW, UNEXData.Completed
HAVING (((UNEXProcess.Process)=[forms]![UnexAreas]![location]) AND ((UNEXData.Completed)=False))
ORDER BY Format([unexdata.DueDate],"ww",2);


any help would be brill
Many thanks

Steve

[cyanidem]

Use DatePart function instead of format.
DatePart("ww",[unexdata.DueDate])
DatePart returns integer value, which is sorted properly unlike string returnd from format function.

Make sure you set additional parameters for this function according to your needs (firstdayofweek, firstweekofyear). Check syntax here: https://msdn.microsoft.com/en-GB/lib...(v=vs.90).aspx

[sdel_nevo]

Hi cyanidem

Many thanks for the reply, that worked brill
and many thanks for the explination, this has to be one of the best sites on the web and thats down to guys like you who use it

many many thanks
Steve