Format Month As Date In Access Query

[geekette]

Hi,

I have a query with a field that shows the month for each record:

Month: (Format$([DateOfData],"mmm yy"))

The query results are linked to a pivot table in Excel....my problem is that I need to sort my months in month order in the pivot table but it reads the month as text so puts them in alphabetical order instead.

How can I change this either in the pivot table or the access query (ideally in Access as I have a number of other queries I am building that will include the month)

Thank you...

 

[james_lankford]

can you just select the date normally ?
then put the date field into the pivot table, then right click on the date field in the pivot table, choose Group and Show Detail, then choose Group, then press Shift and choose Months and Years

or maybe you could select another custom field in access

SortDate: (clng(Format$([DateOfData],"yyyymm")))
so you'll get
200812
200901
200902
and so on
then you could use that field to do the sorting and the other field to do the displaying

 

[nirvana_]

In the line

Month: (Format$([DateOfData],"mmm yy"))

just change "mmm yy" to "m yy". It will change the month from string to integer.

e.g. "Oct 08" will be shown as "10 08".

Hope this helps.