<

Mini Charts with Microsoft Access without ActiveX

Published on
10,173 Points
3,573 Views
1 Endorsement
Last Modified:
Approved
Community Pick
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 

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

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
1
Comment
Author:Bitsqueezer
  • 2
2 Comments
 
LVL 24

Author Comment

by:Bitsqueezer
Hi ericpete,

thanks for formatting the article and your review.
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.

I thought I wrote the details about the fonts in the article with:

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.

and:

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, if someone is interested I can attach it here.)

In opposite to Corel I only need 2 fonts (or, if you want, at least 1 for the pie chart method) for this tool to work which should be no problem for any target system...;-)

Cheers,

Christian
0
 
LVL 24

Author Comment

by:Bitsqueezer
Hi ericpete,

OK, so now I implemented both into the article although I'm not sure if Access users are really interested in how to create TTF fonts with CorelDraw...;-) Because this is not really the scope of the article I created a new sub title below the article.

Cheers,

Christian
0

Featured Post

Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

Join & Write a Comment

Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month