sorting columns in crosstab

[Guest]

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?

 

[Guest]

Use MM MMM-YYYY

 

[Eric D via AccessMonster.com]

Add another column to your query.

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

 

[Michel Walsh]

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

 

[MGFoster]

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+2OrBBChB2kk56DP0wwAoMIa
aTcIErZD87MTBYW7f1P5OYRm
=RzoV
-----END PGP SIGNATURE-----

 

[MGFoster]

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

 

[Duane Hookom]

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.