Format Month abbreviation

[jbair via AccessMonster.com]

I have a crosstab query as follows

TRANSFORM Sum(NCCodeMonthlySums.NCTotals) AS SumOfNCTotals
SELECT Val([NCMonth]) AS NCMonthSort
FROM NCCodeMonthlySums
GROUP BY Val([NCMonth])
ORDER BY Val(Mid([NCCode],3))
PIVOT Val(Mid([NCCode],3));

I want to format NCMonth ( which is now a month number) to abreviated text ie.
OCT DEC etc. I'm just a little lost on the formatting, any help will be very
much apreciated.
Jerry

 

[Jerry Whittle]

Create a table of Months with records like:

1 Jan
2 Feb
etc.

Join this table to NCCodeMonthlySums in a query and display the abbreviated
months. Use this new query as the basis of the crosstab.

But this will create another problem. The columns will now be Apr, Aug, etc.
instead of Jan, Feb, ect. To fix this you'll need to use the Column Headings
property of the crosstab query.

 

[John Spencer (MVP)]

If you are using Access 200o you can use the MonthName function

MonthName(Val(NcMonth),True)

If you want to have the records in month order you will still need the number
column for sorting purposes.

TRANSFORM Sum(NCCodeMonthlySums.NCTotals) AS SumOfNCTotals
SELECT MonthName(Val(NcMonth),True) as NCMonth
FROM NCCodeMonthlySums
GROUP BY Val([NCMonth])
, MonthName(Val(NCMonth),True)
ORDER BY Val(Mid([NCCode],3))
PIVOT Val(Mid([NCCode],3))
ORDER BY Val([NCMonth])

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[jbair via AccessMonster.com]

Thanks guys works great I had to remove the last last Order By staement
though.
Jerry

If you are using Access 200o you can use the MonthName function

MonthName(Val(NcMonth),True)

If you want to have the records in month order you will still need the number
column for sorting purposes.

TRANSFORM Sum(NCCodeMonthlySums.NCTotals) AS SumOfNCTotals
SELECT MonthName(Val(NcMonth),True) as NCMonth
FROM NCCodeMonthlySums
GROUP BY Val([NCMonth])
, MonthName(Val(NCMonth),True)
ORDER BY Val(Mid([NCCode],3))
PIVOT Val(Mid([NCCode],3))
ORDER BY Val([NCMonth])

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

I have a crosstab query as follows

[quoted text clipped - 9 lines]

much apreciated.
Jerry