Chart Axis (1 Viewer)

mounty76

Registered User.
Local time
Today, 05:07
Joined
Sep 14, 2017
Messages
341
Hi All,

I've a chart based on a query, the query is all in order but the chart X axis is not, any ideas on how to make this correct?

Cheers

1632773661020.png
 

isladogs

MVP / VIP
Local time
Today, 12:07
Joined
Jan 14, 2017
Messages
18,186
It is sorting the text fields correctly in alphabetical order
You want to sort in chronological order

Add an extra field YearMonth to the query source where e.g. Apr 2022 = 202204 and sort the X-axis by that
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 07:07
Joined
Feb 28, 2001
Messages
27,001
OR... in the query, concatenate "CDate(#1 " on the front and ")#" on the back of those dates, then plot an X/Y scatter graph. If you convert the text dates to date-format data, the chart will go in the correct order.
 

mounty76

Registered User.
Local time
Today, 05:07
Joined
Sep 14, 2017
Messages
341
OR... in the query, concatenate "CDate(#1 " on the front and ")#" on the back of those dates, then plot an X/Y scatter graph. If you convert the text dates to date-format data, the chart will go in the correct order.
This seems like the better option but I cannot seem to get it to work, this is my SQL:

SELECT [2022 Monthly Totals].[Budget Month], CDbl([SumOfBudget]) AS Budget, CDbl(Nz([Sum Of Home Currency (USD)],0)) AS Actual
FROM [2022 Monthly Totals] LEFT JOIN [tblExpenses Query1] ON [2022 Monthly Totals].[Budget Month] = [tblExpenses Query1].[Date By Month]
ORDER BY CDate("1 " & [Budget Month]);

Any ideas?
 

isladogs

MVP / VIP
Local time
Today, 12:07
Joined
Jan 14, 2017
Messages
18,186
I know the method I suggested works and its easy to do.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 20:07
Joined
May 7, 2009
Messages
19,175
see our discussion.
chart1.png
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 07:07
Joined
Feb 28, 2001
Messages
27,001
Code:
ORDER BY CDate("1 " & [Budget Month]);

Nope, missed it. This should read

Code:
ORDER BY CDate("#1 " & [Budget Month]) & "# ;"

What that does is bracket the date with octothorpes (a.k.a. pound sign). You had a text field, which is not a date, so you needed to bracket BOTH sides of the text field with the # character and convert THAT to a date.
 

Users who are viewing this thread

Top Bottom