Leadership

Display meaningful graphics in Access reports

Meaningful graphics, by virtue of simple wingdings and some modifications to control settings, can add visual value to your Access reports.

Although you can overdo a good thing, a meaningful graphic or two can help readers quickly glean a report or a record's meaning. For instance, the small triangle in the report header is a clear indication that second quarter sales are up from the first quarter. (A quick glance at the totals confirms this.) The graphic is the result of an Iif() function and a wingding (font).

If using Access 2010, turn off Layout Review as follows, before adding the graphic:

  1. Open the report (Northwind's Yearly Sales Report), in Design view.
  2. Right-click the Q2 label. Or, select all of the controls in the Page Header section, by clicking the vertical ruler just to the left of the Page Header section. You'll probably find it easier if you remove Layout Review from all of the controls.
  3. Choose Layout from the resulting context menu.
  4. Then, select Remove Layout.

To add the graphic, do the following:

  1. With the report in Design view, position a label-less text box over the right portion of the Q2 header label.
  2. Double-click the new control to open its property sheet.
  3. Name the new text box txtIcon. (You can skip this step, but it's a good idea to name custom controls.)
  4. Enter the following expression as its Control Source: =IIf([txtQ2Total]>[txtQ1Total],"p","q").
  5. With the control still selected, choose Wingdings 3 from the Font dropdown in the Font group (on the contextual Format tab).

Two things need a bit more explanation. First, the expression compares two values, txtQ1Total and txtQ2Total. These controls, in the report's footer, return the total for the first and second quarters, respectively. If the second quarter sales are greater than the first quarter sales, the expression returns the letter p. If the condition is false, the expression returns q.

That brings us to the second explanation: the Wingdings 3 font displays an up-facing triangle and a down-facing triangle, instead of the letters p and q, respectively. I've only added one graphic to the report, but you could add one to each label, updating the expression to reflect the appropriate quarters.

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

3 comments
ssharkins
ssharkins

You can definitely handle the equal condition separately -- there are many possibilities!

jy76
jy76

Nice idea to make things quicker / easier to read. However, this is just a thought about the article: the expression you suggested will show the downward arrow for Q2 if sales are equal to those in Q1. Although it is perhaps not very likely, handling that situation would seem prudent to me. Many of us have ignored the possibility of something happening because it was 'so unlikely', only to find it occurring...

Editor's Picks