sorting columns in crosstab

I have months (MMM-YYYY) in the columns in the crosstab query. I need to
sort them according to time and not alpahnumeric. With MMM-YYYY formt they
are listed ini alphabetical order, so Apr-2005 appears before Oct-2004. Any
ideas how to accomplish this?

Use MM MMM-YYYY

"monia13" wrote:

I have months (MMM-YYYY) in the columns in the crosstab query. I need to
sort them according to time and not alpahnumeric. With MMM-YYYY formt they
are listed ini alphabetical order, so Apr-2005 appears before Oct-2004. Any
ideas how to accomplish this?

Add another column to your query.

MyDate: FormatDateTime([YourDateField], 2)
Sort by this column


monia13 wrote:
I have months (MMM-YYYY) in the columns in the crosstab query. I need to
sort them according to time and not alpahnumeric. With MMM-YYYY formt they
are listed ini alphabetical order, so Apr-2005 appears before Oct-2004. Any
ideas how to accomplish this?


--
Message posted via http://www.accessmonster.com

Hi,



ORDER BY CDate( "1-" & monthAndYear )



I assume that "1-Oct-2004" is a valid date, with your Regional Settings.



Hoping it may help,
Vanderghast, Access MVP





"monia13" wrote in message
...
I have months (MMM-YYYY) in the columns in the crosstab query. I need to
sort them according to time and not alpahnumeric. With MMM-YYYY formt
they
are listed ini alphabetical order, so Apr-2005 appears before Oct-2004.
Any
ideas how to accomplish this?

monia13 wrote:
I have months (MMM-YYYY) in the columns in the crosstab query. I need to
sort them according to time and not alpahnumeric. With MMM-YYYY formt they
are listed ini alphabetical order, so Apr-2005 appears before Oct-2004. Any
ideas how to accomplish this?

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

The usual way is to set up the date as a numeric instead of an
alphanumeric. E.g.:

Apr-2005 would be shown as 2005/04 - Format(Date_Column,"yyyy/mm")

If you know the date range (years) you can set up the PIVOT clause like
this:

PIVOT Format(date_column,"yyyy/mm")
IN ("Jan-2005","Feb-2005","Mar-2005")

--
MGFoster:::mgf00 at earthlink decimal-point net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQyCi2YechKqOuFEgEQLAigCfTEJppwN9+2OrBBChB2kk56 DP0wwAoMIa
aTcIErZD87MTBYW7f1P5OYRm
=RzoV
-----END PGP SIGNATURE-----

MGFoster wrote:

PIVOT Format(date_column,"yyyy/mm")
IN ("Jan-2005","Feb-2005","Mar-2005")


That should be

PIVOT Format(date_column,"mm-yyyy")
IN ("Jan-2005","Feb-2005","Mar-2005")

--
MGFoster:::mgf00 at earthlink decimal-point net
Oakland, CA (USA)

A third try:
That should be

PIVOT Format(date_column,"mmm-yyyy")
IN ("Jan-2005","Feb-2005","Mar-2005")

I don't ever use date values as headings in a crosstab. Next month, this
query won't work. Relative months are much more flexible.

--
Duane Hookom
MS Access MVP


"MGFoster" wrote in message
ink.net...
MGFoster wrote:

PIVOT Format(date_column,"yyyy/mm")
IN ("Jan-2005","Feb-2005","Mar-2005")


That should be

PIVOT Format(date_column,"mm-yyyy")
IN ("Jan-2005","Feb-2005","Mar-2005")

--
MGFoster:::mgf00 at earthlink decimal-point net
Oakland, CA (USA)