XTab Colun Heading Date Sorting

[mikeTTI]

I have a cross tab query for which I want to display the dayes in mmm-yy format. I am grappling with the problem that this is essentially a string, so gets sorted alphanumerically.

Having read around related forums I have tried basing the XTab on a select query, which is sorted by the conventialal date, but also has another field that gives a date using Format([DateAdded],"mmm-yy").

Trouble is, Access only lets me bring across 1 column heading, so I must sort on the column I display, and this doesn't solve the problem.

The coulmn headings are not be fixed (i.e. "Jan";"Feb" etc) as the months are financial year months (April - March).

I can get this too work by using yyyy/mm, but my board of directors are not happy with this format.

Any ideas. I would be open to re-querying my Xtab from another query of from a report if that worked.

Cheers

Mike

 

[mdb4me]

You can specify the mmm-yy order by bringing up the properties dialog for the query and typying in the order in the column headings property or you could do the same if you construct your query in code. See the PIVOT line below:-

TRANSFORM Count(Orders.OrderID) AS CountOfOrderID
SELECT Customers.Country
FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
GROUP BY Customers.Country
PIVOT Format([ShippedDate],"mmm-yy") In ("Aug-06","Sep-06","Oct-06","Nov-06","Dec-06","Jan-07","Feb-07");

 

[mikeTTI]

Thanks for the help. I have also found another solution I use the format:

"yyyy/mm (mmm-yy)"

This is ugly, but sorts OK and is readable bythose whoe struggle with 2007/01format.

 

[DALeffler]

How about sorting by:

Format([DateAdded],"yyyymm...............mmm-yy")

Use spaces instead of "." in the format property of the control - only used here to show the spacing between...

Then right align your report control (can grow = no, can shrink = no) so only the mmm-yy is displayed.

 

[mikeTTI]

Thanks for your suggestion Doug, that is rather clever.

Strangely I now have another problem. If I specifiy the format of the column headings as anything other than "mmm" I get an error message.

The error message relates to a form I use as a dialog box to set criteria prior to opening the query. If I don't use "mmm" as the format for the column headings, then I get message saying Microsoft jet doesn't recognise the reference to the text boxes on the form as valid feilds. If I leave as "mmm" no problems.

Very puzzling.

I have to copy and paste the query into a monthly board report. So I have decided to go with "mmm" format but change the order to suit our financial year, and insert another row in the board report which has the year.