Hello,

I have set up a chart in one of my reports that pulls data from a query and plots the information. Everything works great except for the labeling of the x-axis (dates).

What I need is for it to go for example Oct '03, Nov '03 and so on.. However, what I am getting is Oct '03, Oct '04, Nov '03, Nov '04 and so on.

Is there anyway of formatting the information so that it appears as it does in the query (properly sorted).

Currently, I am using the code below however if there is any possibility of modifying the chart so it appears as "mmm yy" and maintaining the proper sort would be an a whole lot better.

SELECT (Format([ServicePeriodEnd],"yyyy-mm")) AS Expr1, Sum(QueryReport.ApprovedAmount) AS SumOfApprovedAmount
FROM QueryReport
GROUP BY (Format([ServicePeriodEnd],"yyyy-mm")), (Year([ServicePeriodEnd])*12+Month([ServicePeriodEnd])-1);

Thank you for any information in advance,

Misha

You can add an Order By clause to your SQL statement:

SELECT (Format([ServicePeriodEnd],"yyyy-mm")) AS Expr1, Sum(QueryReport.ApprovedAmount) AS SumOfApprovedAmount
FROM QueryReport
GROUP BY (Format([ServicePeriodEnd],"yyyy-mm")), (Year([ServicePeriodEnd])*12+Month([ServicePeriodEnd])-1)
ORDER BY ServicePeriodEnd;

George

George,

Thanks for the reply,

The order works with the previous code not needing the Order by clause. What I am looking to do is not have it show up as 2004-12 but have it look like Dec '04 and have that ordered.

Basically, I need the format to be "mmm" "'yy" and be ordered. When I try doing that in Access I get an error "You tried to execute a query that does not include the specified expression "ServicePeriodEnd" as part of an aggregatte function.

Here is the SQL code:
SELECT (Format([ServicePeriodEnd],"mmm"" '""yy")) AS Expr1, Sum(QueryReport.ApprovedAmount) AS SumOfApprovedAmount
FROM QueryReport
GROUP BY (Format([ServicePeriodEnd],"mmm"" '""yy")), (Year([ServicePeriodEnd])*12+Month([ServicePeriodEnd])-1)
ORDER BY QueryReport.ServicePeriodEnd;


Edited by: Misha33183 on Wed Sep 6 9:47:05 EDT 2006.

My bad. Try this.

SELECT Format([ServicePeriodEnd],"mmm"" '""yy") AS Expr1, Sum(QueryReport.ApprovedAmount) AS SumOfApprovedAmount, Format([ServicePeriodEnd],"yyyy mm")
FROM QueryReport
GROUP BY Format([ServicePeriodEnd],"mmm"" '""yy"), Year([ServicePeriodEnd])*12+Month([ServicePeriodEnd])-1
ORDER BY Format([ServicePeriodEnd],"yyyy mm");

I am still having that issue of "you tried to execute a query that does not include the specified expression 'Format([ServicePeriodEnd], "yyyy mm")' as part of an aggregate function.

Thanks so much for your help.. I figured out why I kept getting that error.


Again thank you

It's always better when you figure things out yourself. Good luck.

George