Date order in Chart

[fredk]

I have a chart where I compare 2001 data vs 2002 data by month - In the query, I linked a table that contained the names of the months (to get the query and basic report data in time order (monthly)

However, when I use the query to create a chart, the order changes to alphabetically.

Can someone advise how I can get the chart in month order? The current row source is:

SELECT [Month],Sum([Year 1 Contracts]) AS [SumOfYear 1 Contracts],Sum([Year 2 Contracts]) AS [SumOfYear 2 Contracts] FROM [qryYearlyComparison3] GROUP BY [Month];

Thank you very much!

 

[tsumrall]

You shouldn't need to link to get Month names. Use format.
Back up to the qry that has the date ... maybe qryYearlyComparison3? then

for full months name use

FullMonth:format([mydate], "MMMM&quot

To go with it so it is in order do

InOrder:format([mydate], "YYYY mm dd&quot

Set InOrder sort to ASCending and uncheck it so it doesn't try to graph it

or

Add a number field to the month table and enter 1-12. Add it to your query, make it's sort ASC, and uncheck it so it doesn't show.

 

[fredk]

Perfect - I had tried your 2nd suggestion but instead of going back to the query, I tried to add it back in under the data source (add the month table) in other words, the wrong way!

Thanks very much - that got it - I am actually going to implement your first suggestion as it is more efficient - I did try this but was unable to nail it -

Thanks again, I really appreciate it - Have a great weekend!!!

Fred

 

[PeeweeNo1]

I am having a similar problem to this and found this old message - Basically I want to order some data by months in a chart. The data runs through academic years however, so the months run from for example Sept 2002 to August 2003. I can get the data to appear in the correct order in the query by using format:

Month:format([Date], "mmm&quot - for the month name, eg Jan, Feb etc

and then order it by saying
Order:format ([Date], "YYYY mm&quot
and sorting this by ascending.

However, the chart in the report is still ordering the months alphabetically and I don't know why!?!?

Can anyone help please?

Leigh

 

[dhookom]

Leigh,
What is the ROW SOURCE property of you chart control? This sql will determine the order in your graph.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[PeeweeNo1]

I've managed to sort this now thanks, by changing the format of

Month:format([Date], "mmm&quot - for the month name, eg Jan, Feb etc

to

Month:format([Date], "mmm yy&quot - for the month name, so it now reads - Jan 02, Feb 03 etc

This now orders it correctly - Thanks anyway

Leigh