crosstab query by week

[Guest]

I have a query that summarizes closed cases by customer rep for each week.
The results of the query have the data in this format:

CR Name; Week Of; Count

"Week Of" is the date the week begins (in this case it's a Saturday...)

When I make a crosstab off this query I can use "Format([Week Of],"ww")" as
my column heading, sort it ascending, and the week numbers are in consecutive
order. But I don't want the week numbers, I want the original date to be the
heading...when I use Format([Week Of],"Short Date") the column heading has
the date as I want, but the order is seemingly random...

This is my first try at using Cross tabs in Access, so I'm a little confused
as to why the Short Date format won't sort properly.

TIA

 

[John Vinson]

When I make a crosstab off this query I can use "Format([Week Of],"ww")" as
my column heading, sort it ascending, and the week numbers are in consecutive
order. But I don't want the week numbers, I want the original date to be the
heading...when I use Format([Week Of],"Short Date") the column heading has
the date as I want, but the order is seemingly random...

If you look carefully, you'll see that it's not random: it's
alphabetic. The Format function returns a Text String, and the text
string "11/01/2004" sorts before the text string "9/18/04", since 1 is
(alphabetically) less than 9.

Try using just [Week Of] as the column heading, and set the format of
the report textbox to display it as desired. It will sort
chronologically if it's a date instead of a string.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps