Full Version: Chart Date Format Issue
UtterAccess Discussion Forums > Microsoft® Access > Access Reports
Misha33183
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
GroverParkGeorge
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)
Orderd 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
Misha33183
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.

THere 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)
Orderr />
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.

THere 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.
GroverParkGeorge
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
Ordery 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");
Misha33183
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.
Misha33183
Thanks so much for your help.. I figured out why I kept getting that error.
Again thank you
GroverParkGeorge
It's always better when you figure things out yourself. Good luck.
eorge
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.