CrossTAb Issue

[valereh via AccessMonster.com]

Hello,
I am having a problem with a CrossTab Query. I am trying to sort by month
dates which are spread over a few years. Unfortunately, Access does not make
any difference between, for example, June 2005 and June 2004 and the value
concerning these two dates appear under the same column. Instead of simply
having "Jan", "Feb",etc in the columns, how could I do to have "Jan 2005",
"Feb 2005", ect in the columns?

I have been stuck on that issue for days. Thanks,

Valere

 

[Nikos Yannacopoulos]

Valere,

I use a calculated field on the date, like:

Expr1: Year([MyDateField]) & "/" & Month([MyDateField])

and use this field as my column headings. Note that I use year/month
rather than month/year, and the months as numbers, because Access will
sort them as strings, and this is the only way to get the correct sorting.

HTH,
Nikos

 

[Van T. Dinh]

Nikos

You may end up with "2004/10", "2004/11", "2004/12"..."2004/3", "2004/4"
....

and the Columns will be sorted wrongly.

IIRC, I use something like:

Expr1: Year([MyDateField]) & "/" & Format(Month([MyDateField]), "00")

 

[Nikos Yannacopoulos]

Van,

You're absolutely right, that's what I've been using, but it seems my
memory's beginning to fail me once too often!

Thanks,
Nikos