ACC: How to Format a Label for Each Axis of a Graph

• Microsoft Access 2.0, 7.0, 97
• Microsoft Graph version 5.0

SUMMARY

Moderate: Requires basic macro, coding, and interoperability skills.

The Microsoft Access Chart Wizard (or Graph Wizard in Microsoft Access version 2.0) enables you to format a label for each axis of a chart. You can modify the label formats in Microsoft Graph and then save those changes for later use. Under certain circumstances, however, it is not possible to simply reformat the data in Microsoft Graph. To resolve this situation, you should apply the desired formatting before the information is sent to the chart.

MORE INFORMATION

When a chart is created, the nature of the chart and/or the type of grouping may require a temporary modification of the data that is sent to the chart.

This modification takes place in the SQL statement that is generated for the chart by the Chart Wizard. In the example cited for this article, a Date field is converted to an integer number using the Year() function. Because this data is no longer a Date data type, it cannot be formatted using the available Date formats.

When a chart is created using the Chart Wizard, an SQL statement is built. This statement is placed in the RowSource property of the chart object. Each time the form or report in which the chart resides is refreshed, the SQL statement is re-executed and the results are sent to Microsoft Graph.

How to Change the Format of the Labels in a Chart

The following example describes the steps to change the format of the labels in a chart.

For Microsoft Access 7.0 and 97:

1. Open the sample database Northwind.mdb.

2. On the Insert menu, click Form.

3. In the New Form dialog box, select Chart Wizard, select the Orders table, and then click OK.

4. In the Chart Wizard dialog box, under Available Fields, select the OrderDate field and add it to the Fields For Chart list.

5. Repeat step 4 for the Freight field, and then click the Next button two times to accept the defaults.

6. Double-click the X-Axis label, OrderDate By Month.

7. In the Group dialog box, select Year, and then click OK.

8. Click Finish. Note that the labels for the axes are 1994, 1995, 1996 for Microsoft Access 97 or 1993, 1994, 1995 for Microsoft Access 7.0.

1. Open the sample database NWIND.MDB.

2. In the Database window, select the Orders table.

3. On the toolbar, click the New Form button to create a new form.

4. In the New Form dialog box, click Form Wizards.

5. In the Form Wizards dialog box, select Graph, and then click OK.

6. In the Graph Wizard dialog box, under Available Fields, select the Order Date field and add it to the Fields For Graph list.

7. Repeat step 6 for the Freight field, choose Next four times to accept the defaults, and then click Finish. Note that the labels for the axes are 1991, 1992, 1993, 1994.

Changing the Label Format (Microsoft Access 2.0, 7.0, and 97)

1. Open your form in Design view.

2. Select the chart. If the property sheet is not displayed, on the View menu, click Properties.

3. Using the right mouse button, click the RowSource property, and then click Build.

4. Change the field Year([OrderDate]) to Format([OrderDate],"YY").

   NOTE: In Microsoft Access 2.0, there is a space in the Order Date field.

5. Run the query to verify that the years in the OrderDate column are formatted as follows: 89, 90, 91, 92, 93, 94, 95, 96.

6. Close the query and save the changes.

7. Switch the form to Form view. Note that the labels of the axes are now formatted as follows.

        In Microsoft Access 2.0:
   

           89, 90, 91, 92
   
        In Microsoft Access 7.0:
   
           93, 94, 95
   
        In Microsoft Access 97:
           94, 95, 96
   
   

REFERENCES

For more information about editing graphs, search the Help Index for "Graphs," or ask the Microsoft Access 97 Office Assistant.

For more information about this subject in Microsoft Access 1.x, please see the following article in the Microsoft Knowledge Base: