Sorting - Grouping in Reports

[ktrasler]

Hi all

I am using the sorting and grouping in my report, but the report is not sorting the records as intended

This is the date part grouping I'm using.

=DatePart("ww",[Date],1,1)

It is grouping all the weeks but the last week is appearing first. e.g

31/12/06
26/11/06
03/12/06
10/12/06
17/12/06
24/12/06

I want 31/12/06 to appear at the bottom. I have attached a pic of how it is at present.

I have also got the Order By sort and the sorting in the query but still doesn't work.

Cheers

Kev.

 

[Rickster57]

I created a table with a date field.
ADate=12/31/2006 in this format.

I then created a query as follows:
expr1: Format([adate],"dd/mm/yy")
expr2: DatePart("ww",[adate],1,1)

I made a report grouping on expr2 in ascending order and it worked fine on my end.
Doin it this way is OK as long as it is in the same year.

 

[ktrasler]

Hi Rick

I've checked all my sorting and formatting and it all seems to be right.

I have attached a small sample of my database just so you can see the tables query and report.

What am I missing?

Cheers

Kev.

 

[Rich]

Add asecond order by for the date. You should also note that Date is a reserved word in Access and shouldn't be usede as a field name

 

[ktrasler]

DatePart - SOLVED

Hi

I have found out why it was sorting incorrectly.

The original DatePart below was sorting correctly, but by the week number.
Week 1 of this year started on 31/12/06.

=DatePart("ww",[Date],1,1)

I changed the DatePart as below, the difference with this is that the first week of the year will be the first full week.

=DatePart("ww",[Date],1,3)

There may be another way, but this seems like the solution I was after.

I am in the process of changing the Date field names, thanks.

Cheers

Kev.