Sorting By Date

[jonnymenthol]

I have the data below in a query in Access, and I want it to sort by the Week Begin date.
I have put a sort on for 'Ascending' but it doesn't work. (Due to the 29th being after the 28th - regardless of month)

Is there any way I can force the sort in this query ?
It must be in the same query though, as I could probably do it by using this query in another one then sorting that. However, I don't want to do that for reasons I won't bore you with.

WeekBegin Count
05/09/05 137
12/09/05 90
19/09/05 22
26/09/05 55
29/08/05 122

PS - I should mention that I think it is because the WekBegin date is an expression based on an actual date field. Therefore I believe it's treating it as some sort of text field.

Any help would be appreciated.

Thanks.

J.

 

[Crispy]

You could try putting an expression in your query to show the week number and sort on that:

WeekNo: =DatePart("ww",[WeekBegin])

This won't work unless Access is treating your field name as a date/time field. If it's not, you're into the realms of stuff far too complicated for me!

 

[Pat Hartman]

The problem is that your calculated date is being treated as a string rather than a real date. Post the calculation so we can correct it.