Mini Charts with Microsoft Access without ActiveX

A picture says more than thousand words - that's the reason why charts are often used.

Here is a simple method of how you can show a simple bar or pie chart in a standard MS Access form using two TTF fonts. You can use both methods on many ways, either as chart graphic in every row of a continous form or as single chart graphic like shown in the attached demo database.

The fonts are needed to show the chart graphics. The bar chart font uses an extreme small letter width and additionally a nearly zero letter spacing to be able to show a chart bar nearly pixel by pixel. You can of course use other fonts for this effect but you would always have a little spacing between two characters.
The other font is a pie chart font where 180 characters were designed as 180 steps of a pie chart, beginning with a pie of 2 degrees, then 4 degrees and so on until you have a complete filled circle. This is not possible with any other font.

In the result you can have a text field of a user defined size with a user definable scaling showing a more or less wide bar of a chosen type (for example: //// or \\\\ or ||||, but without the spacing between the characters) or you can have a single character field showing the wanted value as little pie chart. Both methods are demonstrated in the attached demo database.

As the output is a simple text string, any control which is able to show a text (and work with an expression) can be used, moreover conditional formatting can change the color depending on the value which is displayed.

To get it working you simply need to install the two TTF fonts which are attached in the file (renamed as "TXT", you must rename it back to "TTF" to get it work).
Then you need to insert the module "modCCMiniChart" into your own database which only contains the following function:

Option Compare Database 
                      Option Explicit 
                      
                      '--------------------------------------------------------------------------------------- 
                      ' Module      : modCCMiniChart 
                      ' Version     : 1.0 
                      ' Author      : Christian Coppes 
                      ' Date        : 19.01.2013 
                      ' Last Change : 26.01.2013 
                      '--------------------------------------------------------------------------------------- 
                      
                      Private dblPercent As Double 
                      Private intPercent As Integer 
                      
                      Public Function fnCCMiniChartValue(dblValue As Double, dblMax As Double, _ 
                                                         Optional strType As String = "Bar", _ 
                                                         Optional strChartChar As String = "|", _ 
                                                         Optional intScale As Integer = 100) As String 
                          If dblMax = 0 Then dblMax = 1 
                          dblPercent = (1 / dblMax) * Abs(dblValue) 
                          
                          Select Case strType 
                              Case "Bar" 
                                  ' Bar chart characters: 
                                  '<>(){}[]|!/\01 
                                  fnCCMiniChartValue = String(dblPercent * intScale, strChartChar) 
                              Case "Pie" 
                                  ' Pie characters are ASCII 33 to 126 and 161 to 246 
                                  intPercent = Int(dblPercent * 180) 
                                  If intPercent + 32 <= 126 Then 
                                      fnCCMiniChartValue = Chr(intPercent + 32) 
                                  Else 
                                      fnCCMiniChartValue = Chr(intPercent + 66) 
                                  End If 
                          End Select 
                      End Function 

Select all Open in new window

This function will be used in the control which should show the chart graphic as expression in the form "=fnMiniChartValue(....)". The control must be set to one of the fonts "CC MiniChart Bars" or "CC MiniChart Pies".

The parameters of the function are the following:

1. First parameter

As first parameter you need to set the value which should be displayed as graphic. That means, enter the name of the control or field in square brackets which contains an integer or decimal number. (If it can be NULL, don't forget to add Nz with a replacement value of 0, like "Nz([MyField],0)" )

2. Second parameter

The next parameter is the value which should be used as 100% value. In the demo database it is simply the value 10,000, in a real database you should get the Max value of the value column and use this as maximum value if it can be a different value each time.

3. Third parameter

The parameter "strType" can be "Bar" or "Pie" which is used to switch between the one or other form of calculation of the result string. In case of a bar chart there will be as many characters of the same character as percentage value was calculated, in case of a pie chart the result is always a single character coming from ASCII 32 to 126 and 161 to 246 which are the different steps of the pie chart (180 steps, 360° pie in 2° steps).(I made the font using CorelDraw and a little VBA script to generate the graphics, the details are explained at the end of the article.)

The bar font was made using a width of 100 points which is extremly small so the characters can simply build a bar graphics because they are so close together. As all characters can only have the same width in CorelDraw in some cases it is without a distance between the characters and some does have a very small spacing so in the result there is a little pattern depending on the chosen character. That also has the effect that the chosen color is twice as bright as normal. You can choose one of the characters "<>(){}[]|!/\01" which is also the character you need to set with the parameter "strChartChar". Any of these characters produce another kind of bar chart.

The last parameter "intScale" can be used to change the entire width of the chart bar, default is 100. This can be used to scale the width depending on the width of your control, of course you could also use it to calculate a logarithmic bar chart.

(The result chart graphic looks not so good with Windows XP, since Windows Vista/A2007 the display of the result ist better.)

In case of the pie chart you do not need the last two parameters.

In the attached demo database there are no records contained to keep the download small. To insert records, use the button at the upper right corner which will insert 10,000 random records at each click.

To see the different kinds of bar graphics you can use the combobox in the header and the textbox. The first changes the character used to display the bar chart and the second is the scale parameter.

As you can see in the demo there is no other code needed to display the chart, the code in the demo form is mainly for comfort reason and is not needed to display the result.

Have fun in experimenting with it.

Christian

Creating the Pie font using CorelDraw

(For all who are interested in how to script CorelDraw to generate a font.)

The pie font was created using a VBA script. CorelDraw has a VBA integrated. Unfortunately in the OEM version this feature is not available. But any application which has an own VBA object model can also be programmed by automation through an external application. So I simply used an Excel sheet to create the VBA script which opens a CorelDraw application and creates and exports the needed graphics.

This is the script (please notice the comments for details, further explanations see below):

Public Sub CreatePieFont()
                          ' Early binding: Create a reference in the VBA editor to the CorelDraw object library you are using
                          ' (here it was the CorelDraw 11 object library)
                          Dim objCApp As CorelDRAW.Application
                          Dim objCDoc As CorelDRAW.Document
                          Dim objShape As CorelDRAW.Shape
                          Dim objPage As CorelDRAW.Page
                          Dim objLayer As CorelDRAW.Layer
                          Dim objColor As CorelDRAW.Color
                          Dim objExportOptions As CorelDRAW.StructExportOptions
                          Dim objDocExp As CorelDRAW.Document
                          Dim dblStartAngle As Double
                          Dim dblEndAngle As Double
                          Dim i As Long
                          
                          Set objCApp = New CorelDRAW.Application
                          objCApp.Visible = True
                          
                          ' create a new CorelDraw document
                          Set objCDoc = objCApp.CreateDocument
                          objCDoc.Resolution = 300
                          
                          ' and a second one for export
                          Set objDocExp = objCApp.CreateDocument
                          objDocExp.Resolution = 300
                          objDocExp.ActivePage.SetSize 10, 10
                          
                          ' create a CorelDraw color object for black color
                          Set objColor = New CorelDRAW.Color
                          objColor.RGBAssign 0, 0, 0
                          
                          Set objExportOptions = New CorelDRAW.StructExportOptions
                          With objExportOptions
                              '.ImageType = cdrBlackAndWhiteImage
                              .UseColorProfile = False
                          End With
                          
                          With objCDoc
                              ' Use the first page to create the drawing of the circle
                              Set objPage = .Pages(1)
                              With objPage
                                  ' size of the page which will be the size of one character of the font
                                  .SetSize 10, 10
                                  ' CorelDraw uses different layers in one page, the "CreateEllipse" function is only available on a layer
                                  Set objLayer = .Layers(1)
                                  With objLayer
                                      ' create the circle over the complete size of the page
                                      Set objShape = .CreateEllipse(0, 0, 10, 10, , , False)
                                      With objShape
                                          ' create 179 pie graphics (the 180th is an empty character = space), 360° in 2° steps
                                          For i = 1 To 180
                                              If i > 0 Then
                                                  With .Ellipse
                                                      .Type = cdrPie
                                                      .Clockwise = True
                                                      ' these start values are necessary so that the angle starts on the top
                                                      dblStartAngle = 270
                                                      dblEndAngle = dblStartAngle + i * 2
                                                      
                                                      ' 360° is at "3 o'clock" position, at i=90° it must be reduced by 90° to end at 270° at the top
                                                      If dblEndAngle > 360 Then
                                                          dblEndAngle = i * 2 - 90
                                                      End If
                                                      .StartAngle = dblStartAngle
                                                      .EndAngle = dblEndAngle
                                                  End With
                                              End If
                                              ' set a flat fill in black color using the color object above
                                              .Fill.ApplyUniformFill objColor
                                              ' the circle should have no outline
                                              .Outline.Type = cdrNoOutline
                                              ' now copy...
                                              .Copy
                                              With objDocExp
                                                  .Activate
                                                  ' ...the generated circle to the export document...
                                                  .ActiveLayer.Paste
                                                  ' ...because the CorelDraw circle object must be converted to curves as this is the only object type
                                                  ' a TTF font can use
                                                  .ActiveShape.ConvertToCurves
                                                  
                                                  ' export the character to the TTF font. CorelDraw exports always only one character at a time. At the first time
                                                  ' it creates the font, any further export exports one character into the created font.
                                                  ' The only problem is, that the export goes through export addins which cannot be programmed by VBA. So
                                                  ' the automatism ends here, you must choose a character in the export dialog and click export manually, then
                                                  ' the loop creates the next character and so on.
                                                  .Export "C:\CCMiniChart_Pies.ttf", cdrTTF, cdrCurrentPage, objExportOptions
                                                  .ActiveShape.Delete
                                                  ' activate the original document to see the progress
                                                  objCDoc.Activate
                                              End With
                                          Next
                                      End With
                                  End With
                              End With
                          End With
                          Set objExportOptions = Nothing
                          Set objColor = Nothing
                          Set objShape = Nothing
                          Set objLayer = Nothing
                          Set objPage = Nothing
                          Set objCDoc = Nothing
                          Set objDocExp = Nothing
                          Set objCApp = Nothing
                      End Sub

Select all Open in new window

As this uses no features of the host application you can also insert the script in any other VBA editor like Access, Word, Powerpoint and so on.

As you can see in the comments, the loop will interrupted by the export dialog of CorelDraw which is unfortunately not programmable by VBA, so you need to do some manual work during export:

In the first loop the export dialog will ask for the basic informations about the font.
1. Family Name: This will be the name of the font in Windows font lists (like displayed in MS Word). I used "CC MiniChart Pies" for it.
2. The checkbox "Symbol Font" must be selected, otherwise the font will not work.
3. Gridsize: I used 1024
4. Before and space width: both 0.

Then it asks if the information should be saved to the font, click "Yes".

Now (and in any further step of the loop) the dialog to choose a character for export opens and here you must set the character width to 100 and the design size to 720, the base line to 0 (default).

I exported the graphic to the standard latin font to keep all characters in the ASCII area from 0 to 128. Click on the character where you want to export the graphics to, I started with "!" (ASCII 33) to use 32 (space) as empty pie.

Then in any step of the loop choose one ASCII char later, so 34, 35, and so on.
When you reach the character 126 then you need to switch to "Latin Extended" which are the ASCII chars vom 128 to 255. You can only start at 161 here for export, CorelDraw doesn't support to export into the characters before. You can see all already defined characters in the export dialog, they are display in bold.

(Hint: CorelDraw sometimes has some problems with it's exporters so sometimes the font is not exported with the correct settings. I used the free font editor "FontForge" (http://fontforge.org/) to check the font and adjust it.)
CCMiniChart.zip