CrossTAb Issue

  • Thread starter Thread starter valereh via AccessMonster.com
  • Start date Start date
V

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
 
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
 
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")
 
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
 
Back
Top