Convert week "number" to actual number to sort it in column headin

[Tim in SF]

I am trying to use a week number as a column heading in a crosstab query. I
have this formula: Week: Format([order_date],"ww"). That returns the week
number. However, it is in text format, not number format, so it sorts like
text: 1, 10, 11, 12 ... 2, 20, 21, ...3, 30, 31 etc. How do I convert it to
a number so it will sort correctly? or is there another way to make it sort
correctly?

 

[John Spencer]

Try using the DatePart function which returns a number
DatePart("ww",[Order_Date])

Or wrap the format function in the Val function

Val(Format([order_date],"ww"))


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[Duane Hookom]

Try double-format:

Week: Format(Format([order_date],"ww"),"00")