Crosstab Query & Month Names

[croy]

When I create a field in a query like this:

MonthNumber: Month([SurveyDate])

.... it returns just what I expect, the ordinal number of the
month for each month over the 1-year span of the query.

But when I set the format on that query field to "mmmm", all
the months are displayed as either, "January" or "December"
even tho the MonthNumber displays all twelve months
properly.

Is there a way to show month names in a crosstab query?

Thanks,
croy

 

[vanderghast]

That is because you have the NUMBER 1 to 12; you don't have dates anymore,
and these numbers converted to dates to apply the format mmmm, range from
December 31, 1899 to January 11, 1900.

? CDate(1), CDate(12)
1899.12.31 1900.01.11

? Format(1, "mmmm"), Format(12, "mmmm")
December January


You can generate the crosstab by changing the PIVOT from

PIVOT Month(SurveyMonth)

to

PIVOT FORMAT(SurveyMonth, "mmmm")



since SurveyMonth, I assume, is a full DATE time value, not just a number.


Vanderghast, Access MVP

 

[John Spencer]

You are formatting values from 1 to 12 - that is date from Dec 31 1899 to Jan
11, 1900.

You can format the surveyDate to return the month names
Format(SurveyDate,"mmmm")

If you want the months in order then change the last line in the crosstab
query SQL view to read

PIVOT Format(SurveyDate,"mmmm") in ("January","February",...,"December")

Or open the Crosstab in Query Design view and in the query properties set the
Column Headings property to "January","February",...,"December"
and change MonthNumber to MonthName: Format(SurveyDate,"mmmm")

Or use the MonthName function to get the monthName

MonthNumber: MonthName(Month(SurveyDate))

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

 

[croy]

When I create a field in a query like this:

MonthNumber: Month([SurveyDate])

... it returns just what I expect, the ordinal number of the
month for each month over the 1-year span of the query.

But when I set the format on that query field to "mmmm", all
the months are displayed as either, "January" or "December"
even tho the MonthNumber displays all twelve months
properly.

Is there a way to show month names in a crosstab query?

Thanks to all that replied. Using your remarks (which
caused a duh-slap or two), I came up with this, which looks
perfect:

TRANSFORM Count(tblCountSurv.CountSurvId) AS Counts
SELECT Year([SurveyDate]) AS Years, Month([SurveyDate]) AS
MonthNumber, Format([SurveyDate],"mmmm") AS MonthName
FROM tblGeoLoc INNER JOIN tblCountSurv ON tblGeoLoc.GeoLocId
= tblCountSurv.GeoLocId
WHERE (((tblGeoLoc.LocSort) Not Like "8.2" And
(tblGeoLoc.LocSort) Not Like "8.3" And (tblGeoLoc.LocSort)
Not Like "8.4") AND ((tblCountSurv.SurveyDate) Between
#7/1/2008# And #6/30/2009#))
GROUP BY Year([SurveyDate]), Month([SurveyDate]),
DatePart("m",[SurveyDate]), Format([SurveyDate],"mmmm")
ORDER BY Year([SurveyDate]), Month([SurveyDate]),
tblGeoLoc.LocSort
PIVOT tblGeoLoc.LocSort;


Off, and running!