Crosstab Query: Formatting the Column Header qstn

[mirirom]

hi there,

would greatly appreciate suggestions that any of you may have. recently, i created a crosstab query where my column heading is based on a "Months" value pulled from a table (which is already formatted as a date value). I'm trying to format the field in the query so that when the query is run, the date value from "Months" appears as Jan-01, Feb-01 etc etc. right now, is showing the literal values in ascending order, e.g. 2/20/01, 3/31/01, etc etc.

the following expression that i've tried works, however, the sort order for the columns runs alphabetically rather than date value. Expr1 : Format([Months], "mmm yy&quot. seems that the function is causing the sort order to go by a text value rather than the date.

again, any suggestions would be greatly appreciated. thanks

mirirom

 

[sko]

Search on "ColumnHeadings Property" in the Access help file.

 

[mirirom]

right right. checked the help section a while ago and nothing was listed as to why this sort order occurs. i'm not that versed with VB, but it seems to be that the format function in this case is performing some kind of typecast, hence causing the newly formated date values to sort alphabeticaly. yet when i've tested this in select queries, the sort order is correct. dates do in fact follow chronologically regardless of their formats, as they should (by default).

i guess my question is that i'm wondering if there's something in the PIVOT section in my SQL code that i'm missing. i'm posting the query for reference:

TRANSFORM Max(IncomeByMonth.Income) AS MaxOfIncome
SELECT MasterTable.Project_Number, MasterTable.[Project Name]
FROM MasterTable INNER JOIN IncomeByMonth ON MasterTable.Project_Number = IncomeByMonth.Project_Number
WHERE (((MasterTable.[Is it Active])=True))
GROUP BY MasterTable.Project_Number, MasterTable.[Project Name], MasterTable.[Is it Active]
ORDER BY MasterTable.Project_Number
PIVOT IncomeByMonth.Months;

 

[MichaelRed]

Just guessing - "Months" is just the month NAME? As in Jan, Feb, ..., Dec? You probably want the "Months" to be just the year and Month as in "yyyymm" format.

MichaelRed
mred@att.net

There is never time to do it right but there is always time to do it over

 

[mirirom]

actually, MONTHS holds a complete date value as in m/dd/yyyy; it's a calculated value that's placed in the table from a seperate form. due to the calculation(s) involved relating to other data, it's necessary for this field to contain a complete date. however, idealy, i'd like to just show the date value as a mmmyy in this query.

originally, my event code formated the calculated date values rather than having "field properties in the form" do this after the value was calculated. but as i stated, placing a formated date value into the table was causing the overall calcultions to malfunction in other critical parts of the DB.

 

[MichaelRed]

Then you will need to 'create' a field with the yyyymm forma of the date and use it as the piviot.

MichaelRed
mred@att.net

There is never time to do it right but there is always time to do it over

 

[mirirom]

interestingly enough, the field format for Months in IncomeByMonth is formatted as such: mmm yy. when a select query is run, the correct format shows. if you change the format for this date value, the sort order is still chronological. the litteral date value only shows up when i'm running a crosstab query (and yes, i've triple checked to make sure that the correct field is being used).