Ordering

Hi,

I have a report which is based on a table called tblStreetSweeping and
has the fields (Date, TicketNo, Type & Weight). Each finacial year
runs from April to March, the data in the table ranges from April 2006
to July 2007, in my query I have the formula SortMthNum:
Month(DateAdd("m",-3,[Date])) but it just takes in to account the
month and not the year, How do i get the data to be sorted in the way
it displays it April 2006, May 2006, June 2006, july 2006 all the way
thru to july 2007 .
In each month I would like the data broken to the actual pick up day
i.e Monday, Tuesday, Wednesday etc. In my query I have the formula
Day: Format([Date],"dddd") but when I run the report it sorta it
alphabetically, how do you get it to order it Monday, Tuesday,
Wednesday etc?

Confused!!
Fie

Add another calcualted field:
SortYear: Year(DateAdd("m", -3, [Date]))

And another one to solve the day of the week issue:
TheDay: WeekDay([Date])
This will return 1 to 7 (for Sun to Sat), which sorts correctly.

BTW, if your field really is called Date, that could cause problems. This
query should work correctly because you added the square brackets, but
Access is likely to have problems interpreting such a name.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Fie" wrote in message
oups.com...

I have a report which is based on a table called tblStreetSweeping and
has the fields (Date, TicketNo, Type & Weight). Each finacial year
runs from April to March, the data in the table ranges from April 2006
to July 2007, in my query I have the formula SortMthNum:
Month(DateAdd("m",-3,[Date])) but it just takes in to account the
month and not the year, How do i get the data to be sorted in the way
it displays it April 2006, May 2006, June 2006, july 2006 all the way
thru to july 2007 .
In each month I would like the data broken to the actual pick up day
i.e Monday, Tuesday, Wednesday etc. In my query I have the formula
Day: Format([Date],"dddd") but when I run the report it sorta it
alphabetically, how do you get it to order it Monday, Tuesday,
Wednesday etc?

Confused!!
Fie