Modern Chart Reference and How-To

Quick Jump

REFERENCE

Chart Objects

Basic object types for Modern Charts in Access.

Step-by-Step How To Create a Modern Chart

Steps to create the 2 charts in the sample database. Download: ModernChart_s4p__ACCDB.zip . Remember to unblock the zip file before extracting the database.

1. Create a Query to line up the data you want to use for the chart

   As a minimum, a chart needs 2 things: something on the X-Axis (horizontal axis, Category Axis) and something on the Y-Axis (vertical, Value Axis). This example prefaces names with "x" and "y" to make that clear. For the Category axis, because sales are being reported by month, dates are converted to the last day of the month, or End-Of-Month, EOM, using this expression:

   	xDate: DateSerial( Year(DateSale), Month(DateSale)+1, 0) 

   The reason this is done instead of using Format to extract month and year is because Modern Charts need the date data type for proper sorting. Even though the query is sorted, Modern Charts don't respect that as Classic Charts do.

   

   SQL:

   	SELECT DateSerial(
   		Year(DateSale), Month(DateSale)+1, 0) AS xDate
   		, Sum(Sales.Amount) AS yAmount
   		, Year(DateSale) AS Yr
   		, Month(DateSale) AS Mo
   	FROM Sales
   	GROUP BY DateSerial( Year(DateSale), Month(DateSale)+1, 0)
   		, Year(DateSale)
   		, Month(DateSale)
   	ORDER BY Year(DateSale)
   		, Month(DateSale); 

Goto Top  

2. Go to the design view of a form or report. From the Form (or Report) Design ribbon, choose Insert Modern Chart from the Controls group.

   

Goto Top  

3. Choose the chart type. This will be a Clustered Column.

   

Goto Top  

4. The cursor changes to show you are about to insert a chart. Click on the canvas in the upper-left corner where you want the chart to go (for a default-size) or click and drag to define the size.

   

Goto Top  

5. An example chart is shown and the Chart Settings Pane opens on the right.

   

Goto Top  

6. Pick the Data Source, which here is a query called qChart.

   

Goto Top  

7. Once the data source is identified, options open up in the Chart Settings.

   

Goto Top  

8. Choose the field for the Category Axis. Because it is a date and Access sees there is just one value for each month, the axis labels are formatted to show month and year.

   

Goto Top  

9. Choose year for the Legend (Series).

   

Goto Top  

10. Choose amount to display on the y-axis. Nothing changes on the preview since this was already the assumption that Access made.

    

Goto Top  

11. Review and change Chart Settings Format. Note that you can change the display name for the series, which axis to show it on (Primary or Secondary), colors, choose to show data label, as well as add trendlines.

    

Goto Top  

12. Change the Fill Color for a series by choosing the series from the drop-down on the Format tab of Chart Settings and then setting the Fill Color.

    

Goto Top  

13. This company has seasonal sales so it's useful to put months of different years next to each other. Modern Charts give us the ability to have multiple fields in the Category, just as a crosstab allows.

    To make months of a year go next to each other so you can compare each month to the same month in another year, add month (Mo) to the category.

    

    If the date field is removed from the category, we are just left with the month number,

    

    so we won't keep it that way, and for now, put the date field into the Category again.

Goto Top  

14. In addition to Chart Settings, there are lots of Properties you can set. From the Design ribbon, choose Property Sheet or press Alt-Enter to turn it on if it isn't showing.

    

Goto Top  

15. As changes are made in the chart settings for data,

    

    they are reflected on Data tab of the Property Sheet, and you can also change them there.

    

    Modern charts create a TransformedRowSource, which is read-only, and used for the chart. Here is the SQL statement that Access generated based on the settings:

    SQL:

    	TRANSFORM Sum( yAmount ) AS  SumOfyAmount  
    	SELECT Format( xDate , "mmm 'yy")
    	   ,  Mo  
    	FROM  qChart  
    	GROUP BY Format( xDate , "mmm 'yy")
    	, Year( xDate )*12 + Month( xDate )-1  
    	,  Mo  
    	ORDER BY  Mo 
    	, Year( xDate )*12 + Month( xDate )-1 
    	PIVOT  Yr 

    In the SQL, TRANSFORM is the keyword that CrossTab queries use to PIVOT information.

    While the transformed row source is useful for tweaking the appearance, it is frustrating because it can't be changed directly. While you do have control over the Row Source property, the final chart uses the Transformed Row Source.

    In this case, the date format is "mmm 'yy" which is okay but my preference would have been "mmm-yyyy". I would also change GROUP BY to be by year then month, and ORDER BY to be simply by date. These clauses have minds of their own though.

Goto Top  

16. The Format tab of the property sheet is where you can set things such as ranges, display units, formats, text, colors, and sizes for titles. The following properties were set:

    Property	Value
    ChartTitle	Monthly Sales
    HasSubtitle	Yes
    ChartSubtitle	mid-2020 to mid-2022
    PrimaryValuesAxisDisplayUnits	Thousands
    PrimaryValuesAxisFormat	#,##0
    BackColor	RGB(240,250,252)
    BorderStyle	Solid
    BorderWidth	2 points
    BorderColor	RGB(30,70,200)
    Top	0.1 inch
    Left	0.1 inch
    Form Properties
    RecordSelectors	No
    NavigationButtons	No

    Here is the result

    

    Instead of using display units to label the primary axis with just "Thousands", I'd rather give the axis a more descriptive title. Dividing by a thousand can also be done by additing a comma to the end of the format code.

    Changed these properties:

    Property	Value
    HasAxisTitles	Yes
    PrimaryValuesAxisTitle	Thousands of Dollars
    PrimaryValuesAxisDisplayUnits	None
    PrimaryValuesAxisFormat	#,##0,

    Now the chart looks like this:

    

    More could be done. GapWidth and GapDepth can't be set like they can with classic charts; however, classic charts don't have grouping one series into what appears to be different series either. To have wider columns, you can make a crosstab query to use for the row source.

Goto Top  

17. Also in the sample database is a crosstab query and a modern chart using the crosstab for the Row Source. Here's the design view of qChart_Crosstab:

    

    To get the month names to sort right, they are prefaced with a Unicode character using the ChW function. I glanced to see if there are official symbols defined for the months (I thought there were, somewhere) but didn't find them, so sun, moons, and planets were used. The first thought would be to just use the month number itself, but that could be confused for a date. Any symbols can be used as long as it will put the months in chronological instead of alphabetical order. Symbols for each month could also be stored in a table.

    			  
    	Mnth: ChrW( 9787+Month(DateSale)) & Format(DateSale, " mmm") 

    

    Here is the Data tab of the Chart Settings. The Data Source (Row Source property but not what the final chart uses) is a query called qChart_Crosstab.

    Just one field is specified for the category x-axis, which is the expression with a sort character and then the month name abbreviation. No further grouping is specified. Multiple series are chosen for the values y-axes.

    

    Because the source query is already a crosstab, the transformed row source is a totals query that doesn't pivot or transform.

    SQL Transformed Row Source:

    	SELECT [Mnth]
    	, Sum([2020]) AS [SumOf2020]
    	, Sum([2021]) AS [SumOf2021]
    	, Sum([2022]) AS [SumOf2022] 
    	FROM [qChart_Crosstab] 
    	GROUP BY [Mnth] 
    	ORDER BY [Mnth]	

    Here is the Format tab of the Chart Settings, where you can set a display name, color, and more for each series. There don't seem to be any exposed properties on the property sheet for format properties of each series. The chart legend shows the Display Name for each series.

    

Download

Download: ModernChart_s4p__ACCDB.zip

You will probably need to remove Mark of the Web from the ZIP file before extracting. Always take an Access database OUT of a zip file before opening it! Here are steps to unblock: https://msaccessgurus.com/MOTW_Unblock.htm

The download ACCDB database has 2 queries, 2 forms with Modern Charts, and a few tables with sample data.

• qChart
• qChart_Crosstab
• f_Chart
• f_Chart_Crosstab

Reference

Modern Chart Types

Value	Description	Icon	acChart constant
0	Clustered Column		acChartColumnClustered
1	Stacked Column		acChartColumnStacked
2	100% Stacked Column		acChartColumnStacked100
3	Clustered Bar		acChartBarClustered
4	Stacked Bar		acChartBarStacked
5	100% Stacked Bar		acChartBarStacked100
6	Line		acChartLine
7	Line Stacked		acChartLineStacked
8	100% Stacked Line		acChartLineStacked100
9	Pie		acChartPie
10	Combo		acChartCombo

Chart Settings

Toggle the Chart Settings pane on and off in the Tools group on the Design ribbon (near Add Existing Fields and Property Sheet). Several of the chart settings are also on the Property Sheet.

Data Settings

Data Source Tables Queries Both Axis (Category) list of fields Legend (Series) None pick a field from the list Values (Y axis) list of fields

Format Settings

Data Series pick from dropdown Format Data Series Display Name Plot Series on (1) Primary (2) Secondary Series Fill Color Series Border Color Display Data Label Trendline Options (1) None (2) Linear (3) Exponential (4) Logarithmic (5) Polynomial (6) Power (7) Moving Average Trendline Name

Properties and Objects

Chart-specific Properties for Modern Charts

A Modern Chart object has the following properties and objects specific to charts. When these were enumerated, several of the data types reported as byte were changed to Integer or Long to match Help (which may not be right!), where Help even listed a data type.

When enumerating the properties, the TypeName function was used to get data type but often it was reported as Byte and that isn't necessarily right. For instance, a color number can be Long, so all the properties storing a color number are set to Long (of course, I have an Access database to track this information and generate HTML, smile).

Where values come from a list, there is a link to the enumeration, or to a page that has it. Enumerations and Lists are also shown on this page after properties. Press Alt-LeftArrow to go back to your previous spot on the page.

General Properties for Modern Charts

Here are some general properties that modern charts have. There is no chart-specific help for these, so mostly textbox help is pulled up when you click a link. Used subform for LinkMasterFields and LinkChildFields since they work the same way.

Some of the properties only have a name and a data type (which may not be right). I found them, but I don't know what they are (yet). Perhaps undocumented goodies?

TypeName reported some data types that I know aren't right. Where Help reports a different data type, it is listed in the description.

Enumerations and Lists

The following shows enumerations and lists for built-in constant types and chart properties.

AcAggregateType

Long
type of aggregation to apply to a set of values

AcAxisRange

Long
behavior for representing minimum and maximum values on an axis -- Auto or Fixed

AcAxisUnits

Long
unit of measurement for values displayed on an axis

AcChartType

Long
choices for chart type

AcColorIndex

color number from 0 to 15

AcControlType

control type such as Label, Textbox, ComboBox, etc

AcDashType

Long
list choices for dash type

AcDateGroupType

Long
list choices for DATE grouping types

AcHorizontalAnchor

Used with the HorizontalAnchor property to specify how a control is anchored horizontally within its layout

AcLayoutType

type of layout

AcLegendPosition

position of a legend relative to its associated chart

AcMarkerType

Long
marker shape for plotting a Line series

AcMissingDataPolicy

Long
plotting strategy of a series when its chart type is acChartLine and values are missing

AcSection

section of a form or report

AcTrendlineOptions

Long
type of trendline to render for a chart series

AcValueAxis

Long
list of value axes in a chart

AcVerticalAnchor

Used with the VerticalAnchor property to specify how a control is anchored vertically within its layout

GridlineStyle

Byte
gridline style such as transparent (default), solid, dashes, etc

GridlineWidth

Byte
gridline width

SpecialEffect

special effect such as flat or raised

System Color constants

Long
named constants for theme colors

ThemeColorIndex

index values corresponding to a theme description such as Text 1 or Accent 4

Events

A Modern Chart object has the following events:

• Click
• DblClick
• Enter
• Exit
• GotFocus
• KeyDown
• KeyPress
• KeyUp
• LostFocus
• MouseDown
• MouseMove
• MouseUp

Methods

A Modern Chart object has the following methods

• Move
• Requery
• SetFocus
• SizeToFit

Backstory

Like many others, I used to skip charting with Access and go straight to Excel ... not any more.

I've used classic charts a long time (many years). For flexibility and variety, modern charts are a bit disappointing. However, modern charts are nice when you want to put them in the detail section of a continuous form since that's something you can't do with classic charts. Modern charts also have a PreviewLiveData property (default = True) so you see changes in the design view as you make them. Modern charts have the basics, so they can display what you'll want in most cases.

CAUTION: before you UNDO on a Modern Chart, save your form or report! UNDO might go back too far and if that happens, the best way to recover might be to close without saving, then go to design and manually change what you last did instead. Save often! ... everytime you like it, save it!

I might have gotten a bit carried away with changing the data type in some of the properties listed to what made better sense. Often, Help doesn't specify the data type and guesses had to be made since enumerations seem to report byte too often. There is more research, testing, and documentation to do! Data Type is pretty important to get right. I've done my best to be accurate. If you see errors, please let me know, thanks.

This is a long page, and took a long time to create. If you find it useful, share the link and pass it forward. Support this site ~ thanks

ps, I still use classic charts since they're more powerful, programmable, and still supported! Many seem to find the modern charts easier to create and change, and they definitely look better out of the gate.

Share with others

here's the link for this page:
https://msaccessgurus.com/article/Chart_Modern.htm

Get Help with Access and charting data

Let's connect and team-develop your application together. I teach you how to do it yourself. While we build something great together, I'll pull in code and features from my vast libraries as needed, cutting out lots of development time.

Do you want to add charts to your database? A picture is worth a thousand words, especially if there are a bunch of numbers to make sense of. I can help. I'll share visualization ideas with you and show you how to make it happen. Email me at training@msAccessGurus.com

Donations are much appreciated.

I look forward to hearing from you.

~ crystal

the simplest way is best, but usually the hardest to see