Creating Simple Bar Graphs in Reports in Access 2007

Article
05/05/2014

This content is outdated and is no longer being maintained. It is provided as a courtesy for individuals who are still using these technologies. This page may contain URLs that were valid when originally published, but now link to sites or pages that no longer exist.

Summary: Learn how to create a Microsoft Office Access 2007 report that uses a bar graph to represent numerical values. The solution requires very little effort and does not use any external controls. (4 printed pages)

Office Visual How To

Applies to: 2007 Microsoft Office System, Microsoft Office Access 2007

Ken Getz, MCW Technologies, LLC

April 2009

When you create reports in Microsoft Office Access 2007, it is often useful to display numeric data in a graph. If you only need to display a simple bar graph to represent comparative values, you can use the Rectangle control and a small bit of VBA code instead of using the more complex methods that you would typically use to display graphs in Office Access 2007.

Download the sample database

To create a simple bar graph in your report, you can place a Rectangle control in the detail section of your report, and then set the width of that control during the Format event (when Access lays out each row of data). This article shows you how you can use that to your advantage to create a bar chart by setting the width of the rectangle control to the numeric value in your data.

To begin, right-click the rptGraph report in the sample database, CreateSimpleBarGraph.accdb, and then choose Print Preview. The report, displayed in Figure 1, shows a list of students. For each student, the report displays a score and a bar whose width represents the value of that score.

Figure 1. Sample report with student scores and bar graphs

To create a similar bar graph in your applications, follow these steps:

Create your report, including the text data that you want to display in each row. The sample report uses controls named txtName and txtScore to display the Name and Score fields from tblScores.
Add a rectangle control from the report toolbox and place it next to the data in the detail section. (The sample report uses a rectangle control named rctBar.) The width of the control is not important for now; you will adjust that programmatically later on. The sample report sets the width of the rectangle to the maximum width, four inches. For the sake of appearance, you might want to set the height of the rectangle to the same height as the text boxes that you have already placed on the report.

Figure 2. Sample report in design view
At your option, you can place vertical lines at regular intervals along the maximum length of the bar. The sample report places vertical lines in increments of 25%, but you can place the lines wherever you prefer. If the vertical lines are the same height as the detail section, they appear as continuous lines on the printed report. If you use group headers or footers in your report, you must place the vertical lines in those sections, as well, to make them appear continuous.
To set the width of the rectangle for each row, create the following event procedure in the OnFormat event property of the report's detail section. The event procedure instructs Access to run your new macro each time that it formats a row of data.
```
Private Sub Detail1_Format(Cancel As Integer, _
  FormatCount As Integer)
    rctBar.Width = (txtScore / 100) * (1440 * 4)
End Sub
```
Save and run the report. It should look similar to the report in Figure 1.

As Access lays out the report and prepares to print it, it formats each row of data. As it does so, it runs the VBA code that you attached to the OnFormat event property. That code instructs Access to set the width of the Rectangle control based on the value in a numeric field. Thus, when Access prints that row of data, the rectangle has a width that is proportional to the value in that numeric field.

In the sample report, the maximum width of the rectangle is four inches. If a student has a score of 100%, you want the printed bar to be 4 inches wide. Thus, the following expression evaluates to the number of inches wide that you want the bar to be.

txtScore / 100 * 4

To set the width of the bar from the Format event, however, you must specify the width in twips, not inches, because that is what Access expects. There are 20 twips in a point and 72 points in an inch, so there are 1,440 twips in an inch. To convert the number of inches to twips, multiply the calculated value by 1,440. The following code is the final expression in the sample report, and evaluates to the width of the bar in twips.

(txtScore / 100) * (1440 * 4)

If your report uses a scaling factor other than 100 or a maximum width other than 4, adjust the expression accordingly. In addition, if you measure in units other than inches, you must modify the expression, as well.

Although the bar graph discussed in this article works only for the simplest cases, it is a terrific solution in the appropriate circumstances. It's quick, it's simple, and it produces nice output. To achieve the precise effect that you want, experiment with different shadings, border colors, and gaps between the rows.

Creating Simple Bar Graphs on Reports in Access

Watch the Video

Video Length: 00:03:27

File Size: 3.27 MB

File Type: WMV

Share via

Creating Simple Bar Graphs in Reports in Access 2007

Additional resources