Date order

[honeypot3]

Hi there

I have an inserted chart which is running the following SQL crosstab for its results:

PARAMETERS [forms]![dateselect]![month] Text ( 255 ), [forms]![dateselect]![year] Text ( 255 ), [forms]![dateselect]![tomonth] Text ( 255 ), [forms]![dateselect]![toyear] Text ( 255 );
TRANSFORM Sum(ContractQuestLinkAvg2.AvgOfResp) AS SumOfAvgOfResp
SELECT ContractQuestLinkAvg2.[Question Text]
FROM ContractQuestLinkAvg2
GROUP BY ContractQuestLinkAvg2.QuesID, ContractQuestLinkAvg2.[Question Text]
ORDER BY ContractQuestLinkAvg2.QuesID
PIVOT Format([CalcDate],"mmm-yy");

I need the [calcdate] field which is a calculated field using Cdate to display in date order. If I use ascending in the design view it just displays in them in alphabetical order on the legend of the graph. Thanks

 

[PHV]

Something like this ?
PIVOT Format([CalcDate],"yy-mm(mmm)");

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[honeypot3]

Hi PHV

Thanks for that it has changed the order to be correct inside the chart legend but now instead of just showing Feb-07 against a legend entry it now shows 07-02(Feb-07) - how can I hide the 07-02 bit so the legend just shows Feb-07?

Sandra