Can't Put Field in Ascending Order

[ChopinFan]

Hi,

I have a field I want to display on a report in ascending order, but even when I specify ascending in the query, it is still out of order.

The field is:
WeekNum:Format([date],"ww")

When I put sort:ascending, it displays in "order" based on the first digit, for example:

1 10 11 12 13 14 15 16 17 18 19 2 21 22 23 24 25 26 27 28 29 3 31 32 33 ... etc.

How can I get this in order?
Thanks!

 

[GingerR]

Access is considering that to not be numeric, so you need a numeric or date type field to sort by. Bring your [DATE] field into the query and sort by it. Take out the sort for WeekNum.

 

[ChopinFan]

Yea, that would work, but the problem is, it's an aggregate query and I can't list individual dates. I even tried splitting it into a sub query but so far haven't been able to get that to work for me either.

?

 

[GingerR]

I was afraid of that. Convert it to a number then:

WeekNum:cdbl(Format([date],"ww"))

 

[ChopinFan]

Wonderful!
Thanks!