Date formatting in moder charts

[Ravi Kumar]

Dear all ,

I have created bar charts in my report using modern chart which has 3 data :
1. Month - in X-axis
2.Sum of complaints: in y - axis
3. Type of issue - category

My sql cose of the query(source data) is like this:

SELECT DISTINCTROW Format$([Data for issue status].[Complaint Received On],'mmmm yyyy') AS [Complaint Received On By Month], [Data for issue status].[Type of issue], Sum([Data for issue status].[CountOfComplaint Number]) AS [Sum Of CountOfComplaint Number]
FROM [Data for issue status]
GROUP BY Format$([Data for issue status].[Complaint Received On],'mmmm yyyy'), [Data for issue status].[Type of issue], Year([Data for issue status].[Complaint Received On])*12+DatePart('m',[Data for issue status].[Complaint Received On])-1;

My SQL for chart is like this :

TRANSFORM Sum([Sum Of CountOfComplaint Number]) AS [SumOfSum Of CountOfComplaint Number] SELECT [Complaint Received On By Month] FROM [data for chart month wise] GROUP BY [Complaint Received On By Month] ORDER BY [Complaint Received On By Month] PIVOT [Type of issue]

My SQL for chart axis is like this:
[Complaint Received On By Month]

I need solution for below two issues:

1. The X-axis is sorting based on text format , but actually it is month (for ex it is showing February 2020 first then January 2020), how to correct this?
2. Can a trendline be inserted to the chart?

3. And also when my duration form an error message is coming , how can I solve this?

I have attached my database , please check once

 

[The_Doc_Man]

Regarding the "monthly complaint" chart's X-axis, if you want to keep things in calendar order, don't convert them to something else, OR (since it is a query and queries are cheap) order by a date but have a 2nd column that shows the month and year for the category axis. In other words, have the date unformatted for sorting but have a parallel translation of the same field for display & reporting purposes.

I've never tried trendlines in Access so have no idea how to do that with VBA. But the ability supposedly exists.

As to the error message... what is it and what specific action triggers it?

Ravi Kumar, it is not necessary and may even be ineffective to send a message to me to ask me to look at something. In general, I look at things where I have expertise. I would not normally have stepped into this one since I have no particular expertise in chart-making. I always do that by hand for my personal databases and always used Excel at work (again, manually). So I might not be the best source of help for this problem. Don't take that as a dig at you but rather just advising you to keep your avenues more open.