Graph displaying dates alphabetically on x axis

[Pascoe via AccessMonster.com]

Hello!

Title says it all - I want the x axis to show dates in DATE ORDER, not
alphabetical order.

I've read thru the forum, can't see how to fix it - your help will be
appreciated.

Kind Regards,
Russell.

p.s. Here is the graph Row Source, should there be an ORDER BY statement in
here?

SELECT (Format([Date Paid],"mmm"" '""yy")) AS Expr1, Sum([Tenant Payments].
Amount) AS SumOfAmount FROM [Tenant Payments] GROUP BY (Format([Date Paid],
"mmm"" '""yy")), [Tenant Payments].[Date Paid], (Year([Date Paid])*12+Month(
[Date Paid])-1) HAVING ((([Tenant Payments].[Date Paid])>=#3/1/2009#));

 

[Duane Hookom]

Try this SQL for the Row Source:

SELECT Format([Date Paid],"mmm"" '""yy") AS Expr1,
Sum([Tenant Payments].Amount) AS SumOfAmount
FROM [Tenant Payments]
Where [Date Paid] >=#3/1/2009#
GROUP BY Format([Date Paid],"mmm"" '""yy"), [Tenant Payments].[Date Paid],
Year([Date Paid])*12 + Month([Date Paid])-1
ORDER BY Year([Date Paid])*12 + Month([Date Paid])-1;

 

[Pascoe via AccessMonster.com]

Duane,

Thank you for your response.

I tried your suggestion, and it certainly moves me in the right direction
(ordered by calendar rather than alphabet), but it seems to have another
effect too. The graph is supposed to be a sum of per month performance, over
a 13 month period, however, now it is displaying a greater granularity, i.e.
Mar 09, Mar 09, Mar 09, Apr 09, Apr 09, May 09, May 09, etc etc

Looking at the datasheet behind the graph I can see that the first 26 entries
are Mar '09, the next 23 are for Apr'09, whereas the way I read the statement,
there should be only one entry for Mar'09, Apr' 09 etc as the statement says
it should be a Sum of amount by month. This graph is now displaying 366
entries on the datasheet across the 13 months - I, of course, want only to
see 13 entries displayed.

Your insight will be much appreciated, because it baffles me!

Kind Regards,
Russell.

Try this SQL for the Row Source:

SELECT Format([Date Paid],"mmm"" '""yy") AS Expr1,
Sum([Tenant Payments].Amount) AS SumOfAmount
FROM [Tenant Payments]
Where [Date Paid] >=#3/1/2009#
GROUP BY Format([Date Paid],"mmm"" '""yy"), [Tenant Payments].[Date Paid],
Year([Date Paid])*12 + Month([Date Paid])-1
ORDER BY Year([Date Paid])*12 + Month([Date Paid])-1;

Hello!

[quoted text clipped - 14 lines]

"mmm"" '""yy")), [Tenant Payments].[Date Paid], (Year([Date Paid])*12+Month(
[Date Paid])-1) HAVING ((([Tenant Payments].[Date Paid])>=#3/1/2009#));

 

[Duane Hookom]

I started with what you had for the Row Source which included the [Tenant
Payments].[Date Paid] in the GROUP BY. Get rid of it.

--
Duane Hookom
MS Access MVP

Duane,

Thank you for your response.

I tried your suggestion, and it certainly moves me in the right direction
(ordered by calendar rather than alphabet), but it seems to have another
effect too. The graph is supposed to be a sum of per month performance,
over
a 13 month period, however, now it is displaying a greater granularity,
i.e.
Mar 09, Mar 09, Mar 09, Apr 09, Apr 09, May 09, May 09, etc etc

Looking at the datasheet behind the graph I can see that the first 26
entries
are Mar '09, the next 23 are for Apr'09, whereas the way I read the
statement,
there should be only one entry for Mar'09, Apr' 09 etc as the statement
says
it should be a Sum of amount by month. This graph is now displaying 366
entries on the datasheet across the 13 months - I, of course, want only to
see 13 entries displayed.

Your insight will be much appreciated, because it baffles me!

Kind Regards,
Russell.

Try this SQL for the Row Source:

SELECT Format([Date Paid],"mmm"" '""yy") AS Expr1,
Sum([Tenant Payments].Amount) AS SumOfAmount
FROM [Tenant Payments]
Where [Date Paid] >=#3/1/2009#
GROUP BY Format([Date Paid],"mmm"" '""yy"), [Tenant Payments].[Date Paid],
Year([Date Paid])*12 + Month([Date Paid])-1
ORDER BY Year([Date Paid])*12 + Month([Date Paid])-1;

Hello!

[quoted text clipped - 14 lines]

"mmm"" '""yy")), [Tenant Payments].[Date Paid], (Year([Date
Paid])*12+Month(
[Date Paid])-1) HAVING ((([Tenant Payments].[Date Paid])>=#3/1/2009#));

 

[Pascoe via AccessMonster.com]

Pure Genius, That does it!

Thanks Duane.

I started with what you had for the Row Source which included the [Tenant
Payments].[Date Paid] in the GROUP BY. Get rid of it.

Duane,

[quoted text clipped - 39 lines]

Paid])*12+Month(
[Date Paid])-1) HAVING ((([Tenant Payments].[Date Paid])>=#3/1/2009#));