Using Calculated Values

Much of the power of Access 2007 reports comes from their ability to perform both simple and complex calculations on the data from the underlying tables or queries. Access also provides dozens of built-in functions that you can use to work with your data or to add information to a report. The following sections provide examples of the types of calculations you can perform.

Adding the Print Date and Page Numbers

One of the pieces of information you might frequently add to a report is the date on which you prepared the report. You’ll probably also want to add page numbers. Access provides two built-in functions that you can use to add the current date and time to your report. The Date function returns the current system date as a date/time value with no time component. The Now function returns the current system date and time as a date/time value.

Note 

When you create a report using the Report Wizard, it adds a similar control to the Page Footer section, and it uses the Now function that returns the date and the time. However, the wizard sets the Format property to Long Date, which displays only the date portion.

To add the current date to your report, create an unbound text box control (delete the label) in the Page Footer section, and set its Control Source property to =Date(). Then, in the Format property box, specify Long Date. You can see an example of using the Date function in Figure 16–8. The result in Print Preview is shown in Figure 16–9.

Figure 16–8: Use the Date function in an unbound control to add the date to a report.

Figure 16–9: You can now see the current date displayed in the report in Print Preview.

In the Controls group on the Design tab, Access 2007 includes a button that helps you create this type of control on your report. Click the Date & Time button in the Controls group, and Access opens the Date And Time dialog box, shown in Figure 16–10. You can choose to insert the date, the time, or both the date and time displayed in a text box control into the report’s Report Header section. You can choose different formats for both. Access displays a sample of what the control will display at the bottom of the dialog box. However, the tool does not offer you any choices of where it will place the control-Access always places this control in the Report Header section on the right side. You could create the control using this feature (selecting only the Include Date check box and the Long Date format), drag and drop it into the Page Footer on the left, set the alignment to left, and shrink the Report Header back to zero height, but we think it’s just as easy to create the control yourself. Click Cancel to close the dialog box because you’ve already added a control to the report to display the date.

Figure 16–10: Use the Date And Time dialog box to assist you in building a report date control.

To add a page number, use the Page property for the report. You can’t see this property in any of the property sheets because it is maintained by Access. Access also provides the Pages property, which contains a count of the total number of pages in the report. To add the current page number to a report (in this example, in the Page Footer section), create an unbound text box control (delete the label), set its Control Source property to =“Page ” & [Page] & “of” -& [Pages] as shown in Figure 16–11, and set the Text Align property to Right.

Figure 16–11: Use the Page and Pages properties to add page numbers to a report.

In the Controls group on the Design tab, Access 2007 includes a button that helps you create this type of control on your report. Click the Insert Page Numbers button in the Controls group, and Access opens the Page Numbers dialog box, shown in Figure 16–12. (Remember, we discussed this command in Chapter 15, “Constructing a Report.”) You can choose to insert the page number or the page number and count of pages. Access also offers an option to display the page text box control in the report’s Page Header or Page Footer section. In the Alignment drop-down list you can choose where to have Access place the control-Left, Center, Right, Inside, or Outside. Note that Access adds two controls for the Inside and Outside options and includes a logical expression in the control source to display each text box on alternate pages. When you choose Inside, even page numbers appear on the right, and odd page numbers appear on the left. When you choose Outside, even page numbers appear on the left, and odd numbers appear on the right. Select the Show Number On First Page check box at the bottom of the dialog box to display the page numbers on all pages, including the first page. If you clear this check box, Access creates a control that will not show the page number on the first page. You can try this feature using the settings in Figure 16–12 to compare the result with the text box you created previously, or you can click Cancel to close the dialog box.

Figure 16–12: Use the Page Numbers dialog box to assist you in building a page number control.

Inside Out-You Can Change the Value of the Page Property in Code 

You can reset the value of the Page property in a macro or a Visual Basic procedure that you activate from an appropriate report property. For example, if you’re printing several multiple-page invoices for different customers in one pass, you might want to reset the page number to 1, when you start to format the page for a different customer. You can include a Group Header section for each customer and then use a macro or a Visual Basic procedure to set the Page property to 1, each time Access formats that section (indicating that you’re on the first page of a new customer invoice).

Performing Calculations

Another task you might perform frequently is calculating extended values from detail values in your tables. If you understand the principles of good table design (see Article 1, “Designing Your Database Application,” on the companion CD), you know that it’s usually redundant and wasteful of storage space to define a field in your tables that you can calculate from other fields. The only situations in which this is acceptable are when saving the calculated value will greatly improve performance in parts of your application and when you have collected static historical data in a table designed specifically to support reporting.

Performing a Calculation on a Detail Line

You can use arithmetic operators to create complex calculations in the Control Source property of any control that can display data. You can also use any of the many built-in functions or any of the functions you define yourself in a module. If you want, you can use the Expression Builder that you learned about in Chapter 7, “Creating and Working with Simple Queries,” to build the expression for any control. You let Access know that you are using an expression in a Control Source property by starting the expression with an equal sign (=).

Note 

To use a field in a calculation, that field must be in the table or query specified in the Record Source property of the report.

One calculated value that housing management might find useful is the daily revenue for each room. You could have calculated that value in the query that is the record source of the report, but you can also calculate it as an expression in a text box in the Detail section of the report. To add the expression you need to the Facility Occupancy By Date report that you have been creating, follow these steps:

1. Before you add your new control to the Detail section, you should first expand the width of the FacilityName control because it is too narrow to display all the facility names. Click the FacilityName control, and drag its left edge toward the left side of the report to make it flush with the left side. Next, drag its right edge toward the right edge of the report to expand the width of the control until it is about 1.6 inches in width. (You can check this in the Property Sheet window if you want.) As you expand the width, Access moves the other controls in the Detail section to the right (along with their labels in the Page Header section). When the Report Wizard created this report, it placed all the controls in the Detail section into a tabular control layout, so when you resize the FacilityName control, Access adjusts the other controls as well. Note that you could make these changes in Layout view in order to see the data while resizing the control. However, Access collapses the height of the two DateValue footer sections and the FacilityName footer section when you switch to Layout view. Go ahead and change the caption for the facility name label as well from Name to Facility by clicking the label and typing Facility.

2. Reduce the width of the DateValue controls to 0.9 inch, the RoomNumber controls to 0.8 inch, and the EmpName controls to 1.6 inches.

3. On the Design tab, in the Controls group, click the Text Box button, and place a text box in the Detail section to the right of the EmpName text box. Select the attached label, and delete it.

4. In the Control Source property of the new text box, enter

    =CCur(Round([TotalCharge]/([CheckOutDate]-[CheckInDate]),2))

   Because you included the TotalCharge, CheckInDate, and CheckOutDate fields in the query, you can reference them in your expression. This expression calculates the daily revenue by dividing the total charge for the reservation by the number of days. Set the Format property of the text box to Currency, and set the Width property to 0.75 inch. Move this text box close to the EmpName text box, and line up the tops of the two text boxes. Also, make sure the height of both text boxes is the same by selecting them both and then clicking the Size To Tallest button in the Size group on the Arrange tab.

5. Click the Line button in the Controls group on the Design tab, and place a horizontal line in the Page Header section below the labels. Move the line to the left edge of the report, and set its Width property to 5.9 inches so that it stretches over all the controls in the Detail section, including your new text box. Make sure the height of the control is 0. inches. Click the Line Thickness button in the Controls group, and select 2, PT for the thickness.

6. Click the Label tool in the Controls group on the Design tab, and draw a label control next to the Employee label in the page header. Type Charge in the label, and press Enter. (If you don’t type anything, the label disappears when you click away from it.)

7. The default Label control in the Access 2007 style has no background color, but the other labels have a light blue background. You can click one of the other labels in the page header, click the Format Painter button in the Font group on the Design tab, and then click your new label to transfer the format.

8. Make the new label the same height as the other labels in the page header, and give it the same width as the text box below it (0.75 inch). Align the left edge of the label with the left edge of the text box, and align the top of the label with the top of the other labels in the section.

Your report in Design view should now look like Figure 16–13.

Figure 16–13: Add an expression to the Detail section to calculate daily revenue.

Figure 16–14 shows the result in Print Preview. (Note that the Access 2007 report style has an alternating color defined for the Detail section.) You can see that Access has performed the required calculations for each day of each reservation.

Figure 16–14: The calculated detail line values within a group now appear in Print Preview.

Inside Out-Avoiding #Error in a Calculated Control 

Instead of starting with an unbound text box to perform a calculation, you might decide to drag and drop one of the fields that you’ll use in the calculation from the field list onto your report When you do that, Access gives the text box the same name as the field. If you modify the control source to an expression that uses the field, you’ll see #Error in the text box when you view or print the report.

When you enter a name in an expression, Access first searches for another control that has that name. If it doesn’t find the control, then it looks in the field list. So, if the name of the control is TotalCharge and you include an expression that uses [TotalCharge], the expression is referencing itself! Access can’t figure out how to display the value of the TotalCharge text box, so it displays #Error instead. If you decide to drag and drop a field that you’ll change to an expression, be sure to change the Name property of the control (for example, txtTotalCharge) before you enter the expression.

Of course, you’ll also see #Error if your expression references a function that doesn’t exist or provides invalid parameters to a function. However, using the name of the control itself inside the expression that is the control source is one of the most common sources of #Error.

Adding Values Across a Group

Another task commonly performed in reports is adding values across a group. In the previous chapter, you saw a simple example of this in a report that used the builtin Sum function. In the Facility Occupancy By Date report, you have three levels of grouping: one by facility, another by month, and another by date. When you specified grouping and sorting criteria earlier in this chapter, you asked Access to provide group footers. This gives you sections in your report in which you can add unbound controls that use any of the aggregate functions (Sum, Min, Max, Avg, Count, First, Last, StDev, or Var) in expressions to display a calculated value for all the rows in that group. In this example, you can create unbound controls in the Facility footer and both DateValue footers to hold the totals by facility, by month, and by date, for the daily charge for each room, as shown in Figure 16–15. In the Control Source property of each, enter

 =Sum(CCur(Round([TotalCharge]/([CheckOutDate]-[CheckInDate]),2)))

Figure 16–15: Add summaries by facility, by month, and by date into the three footer sections.

Set the Format property to Currency for each of the three new controls. Notice that in this case, you must repeat the original expression inside the aggregate function rather than attempt to sum the control you placed in the Detail section. (See “How to Calculate Totals on Expressions” on the next page for an explanation.)

You should also add a line control at the top of each footer section to provide a visual clue that the values that follow are totals. When you place a line control on the grid in any footer section using the Access 2007 report style, Access sets Border Style to Transparent. Change the Border Style property to Solid for each of these lines. In this example, we placed lines approximately 4.85 inches in from the left and made them about 1, inch long, but they’re difficult to see in Design view because they’re right up against the top of the section. Also move the page number control so the right edge lines up with these new calculated controls, and reduce the width of the design grid to 6.5 inches.

Inside Out-How to Calculate Totals on Expressions 

An important point to remember about using an aggregate expression in a group section is that the expression cannot refer to any calculated controls in the Detail section. As you’ll learn later, you can reference an outer control from an inner one (for example, a total calculation in a group from inside the Detail section), but not vice versa. So, you cannot create a calculated field in the Detail section, for example, that multiplies two numbers and then reference that control in the summary expression. You can, however, repeat the calculation expression in the summary. If a detail control named Total has an expression such as =[Quantity] * [Price], you must use an expression such as =Sum([Quantity] * [Price]) in your grouping section, not =Sum([Total]).

Creating a Grand Total

Use the Report Footer section to create grand totals for any values across the entire set of records in a report. You can use any of the aggregate functions in the report footer just as you did in the two grouping section footers. Expand the Report Footer section to about 0.43 inch to give yourself some room. Add a new text box control to the Report Footer section, and set the Control Source property to the same expression used in the three footer controls. Set Format to Currency, and change the width of the text box to 1, inch to allow extra room for a larger number total. Align the right edge of the text box to the other three calculated controls. Click inside the label for this new text box, type Grand Total:, and align the text to the right. Finally, bold the text in the label, and move the label so that its right edge is next to the left side of the text box. Figure 16–16 shows you this Sum function used in the new control in the report footer to produce a total for all records in the report.

Figure 16–16: Use the Report Footer section to create a grand total control for all records.

If you switch to Print Preview, go to the last page in the report, and scroll down, you should see a result similar to that shown in Figure 16–17. (You should set the name of this grand total field to txtSumGrand so that you can use it to calculate percentages later.) You can find this stage of the report design saved as rptXmplFacilityDateOccupancyStep2 in the sample database.

Figure 16–17: You can see the various totals displayed in the report in Print Preview.

Note 

If you want to create percentage calculations for any of the groups over the grand total, you must create the control for the grand total in the report footer so that you can reference the total in percentage calculation expressions. See “Calculating Percentages” on page 843. If you don’t want the total to print, set the control’s Visible property to No.

Hiding Redundant Values and Concatenating Text Strings

You probably noticed in several of the preceding examples that the FacilityName and DateValue fields print for every detail line. When a particular detail line displays or prints values that match the previous line, the report looks less readable and less professional. You can control this by using the Hide Duplicates text box property (which is available only in reports). Switch to the Design view of this report, and set the Hide Duplicates property to Yes for the FacilityName text box and the DateValue text box in the Detail section. The report will now print the facility name and the date only once per group or page, as shown in Figure 16–18. (The figure shows information from the last page of the report.) When Access moves to a new grouping level or page, it prints the facility name even if it matches the previous value displayed.

Figure 16–18: Set the Hide Duplicates property to Yes to eliminate redundant values in each group.

Notice that when the report gets to the end of data for a month or a facility, it’s not clear what the total lines mean. For example, on the last page of the report as shown in Figure 16–18, $792.58 is clearly the total for the last date, but it’s not obvious that $17,047.07 is the total for the month of June for the facility or that $44,479.28 is the total revenue for the facility. You can use string concatenation to display data that looks like a label but that also includes information from the record source. Sometimes it’s useful to combine descriptive text with a value from a text field in the underlying query or table or to combine multiple text fields in one control. In Figure 16–19, you can see a descriptive label (created by a single text box control) on one of the subtotal lines. (In Figure 16–19 we dragged the Property Sheet window below the new text box so that you could see the Control Source property.)

Figure 16–19: A text constant and a string derived from a field in the record source are concatenated as a “label” in a text box.

This “label” concatenates the words Total for with an expression that uses the Format function-applied here to the DateValue field to get the date in medium date formatand an ending string containing a colon. You can use the same technique in the group footer to create a “label” that reads Total for facility followed by the facility name and a trailing colon. You could certainly define a label followed by a text box followed by another label to create the same display. The advantage of using a single control is that you don’t have to worry about lining up three controls or setting the font characteristics. In fact, because the string in the middle, containing the facility name, could vary significantly in length, you cannot create three separate controls that correctly line up all possible values end-to-end. Set the Text Alignment property of these controls to Right so that they line up correctly next to the summary controls, and match the formatting of the grand total label in the Report Footer section by selecting the grand total label, double-clicking the Format Painter button to lock it, and then clicking the three text boxes. Click the Format Painter button again when you’re done to unlock it.

When you look at the report in Print Preview, as shown in Figure 16–20, you can see that the duplicate values for the facility name and for the date have been eliminated. You can also see the nice result from using a concatenated string in a text box to generate labels for the total lines.

Figure 16–20: The total lines now have descriptive captions using data from the record source.

Calculating Percentages

In any report that groups and summarizes data, you might want to determine what percentage of an outer group total or the grand total is represented in a particular sum. You can do this in a report because Access makes two passes through the data. On the first pass, it calculates simple expressions in detail lines, sums across groups, sums across the entire report, and calculates the length of the report. On the second pass, it resolves any expressions that reference totals that were calculated in the first pass. Consequently, you can create an expression in a detail or group summary section that divides by a sum in an outer group or the grand total to calculate percentages.

Figure 16–21 shows an example of a percentage calculation in the FacilityName Footer section. (We dragged the Property Sheet window down below the control so that you could see the Control Source property.) The expression divides the sum of the calculated charge for this facility by the value in a field called txtSumGrand-the name of the grand total control in the report footer. (Remember that when you created the grand total, we instructed you to give it this name.)

Figure 16–21: You can add a calculation in the group footer for a percentage of a grand total.

Set the Format property of the text box to Percent, and switch to Print Preview. Scroll down to find a total by month or by facility, and you’ll also see the percent of the grand total, as shown in Figure 16–22. You can find this stage of the report design saved as rptXmplFacilityDateOccupancyStep3 in the sample database.

Figure 16–22: At the end of the report, you can see percentage calculations for two groups in Print Preview.

Using Running Sum

In addition to producing totals for any group you define, Access lets you create running totals within the Detail section or any group header or footer. For any text box that displays a numeric value, you can set the Running Sum property to produce a total that is reset at the start of each group or that continues totaling through the entire report. Let’s further refine rptXmplFacilityDateOccupancyStep3 to see how this works.

Before you can add any controls horizontally, you need to adjust the sizes of some of the controls to provide more horizontal space within the design width of the report. Select the Room label control, and change the width to 0.5 inch in the Property Sheet window. Access moves the EmpName controls to the left so that they remain close to the Room controls. Click the Employee label control, and set the width to 1.3 inches. Select both the Charge label control and the calculated text box control below it, and slide them both to the left about 0.3 inch. (The Left property for these controls should be about 4.8 inches now.) Click the line control in the page header that runs beneath all the labels, and set its width to 6.45 inches. Select all the controls in both DateValue footers, the FacilityName footer, and the report footer, and slide them to the left so that they now line up with the new position of the charge calculation text box in the Detail section.

Now you have some horizontal room to add another label control and companion text box control. Start by selecting the Charge label control, copy it to the Clipboard, and paste it back into the page header. Move it just to the right of the existing Charge label control, and line it up vertically with all the other labels. Now change its caption to Cum. Charge, and set its width to 0.85 inch.

Inside Out-Select the Section Before Pasting 

If you select the Page Header section before you perform the paste, Access places the control in the upper-left corner of the section and doesn’t change the size of the section. If you leave the original control selected when you perform the paste, Access places the new copy below the original and expands the section, which you might not want it to do.

Likewise, select the text box control below the original Charge label control, copy it to the Clipboard, and paste it back into the Detail section. Move it to the left of the existing charge calculation text box, and line it up horizontally with all the other text boxes. Line up the new text box control with the label control above it, and set its width to 0.85 inch to allow room for larger numbers. Finally, select the new text box control, and set its Running Sum property in the Property Sheet window to Over Group. Your report should now look like Figure 16–23.

Figure 16–23: For the Running Sum property of the new calculated text box, select Over Group to add a running sum calculation on the charge.

No, this isn’t a second copy of the charge calculation. As you’ll see when you look at the report in Print Preview, this produces (as the name of the property implies) a running 04 sum of the charge calculation within the Detail section. As Access encounters each new row in the Detail section, it adds the current value of calculation to the previous accumulation and displays the result. Because you asked for the sum Over Group, Access resets the accumulating total each time it encounters a new group.

Next, let’s use a little trick to generate a line number for each line in the Detail section. To make room for this line number, click the Facility label, and drag its right edge to the right side of the report until it moves the Employee label control and EmpName text box control next to the Charge label control. Now drag the left side of the Facility label to the right to reduce its width and create enough space for a small text control. (The Facility label and FacilityName text box controls should be about 1.65 inches in width when you complete these steps.) Insert a small text box in the space you just created in the Detail section. Above this text box, create a label that displays # as its caption. (You can use the Format Painter button again to copy the format from one of the existing label controls to the new one.)

Remember that as Access formats each detail line, it takes the current value of the field (actually, the current value of the text box), adds it to the previous total, and displays the result. If you set the text box equal to any constant numeric value, Access uses that value for each detail line it produces. So, the trick is to set this text box equal to 1, (=1 in the Control Source property) and then set the Running Sum property. If you choose Over All for Running Sum, Access will number the first line 1, add 1, for the second line and display 2, add 1, for the third line and display 3, and so on throughout the report. If you choose Over Group, Access increases the number for each line but resets the number back to 1, when a new group starts. Select Over Group on the Running Sum property for this control to sequentially number all the reservations on the same date, as shown in Figure 16–24. Also set the Format property of the control to 0. to place a period after each displayed value.

Figure 16–24: Use the Running Sum property to generate a line number.

If you switch to Print Preview, you can see the result of using Running Sum, as shown in Figure 16–25. The charge accumulates over each group and then resets for the next group. The line numbers start at 1, and also reset for each group. You can find this report saved as rptXmplFacilityDateOccupancyStep4 in the sample database.

Figure 16–25: You can see the result of using Running Sum to produce a cumulative total for each group and a line number for each detail line.

Taking Advantage of Conditional Formatting

In Chapter 13, “Advanced Form Design,” you learned how to define conditional formatting for a text box control. Access makes an identical facility available to you for reports Let’s say, for example, that you want to highlight any daily total that is more than $400 or any monthly total that is greater than $10,000. To do this, open the report from the previous example in Design view, select the Sum text box in the first DateValue Footer 04 section that displays the sum of the charge, and click the Conditional button in the Font group on the Design tab. Access displays the Conditional Formatting dialog box, as shown in Figure 16–26.

Figure 16–26: Set conditional formatting for the Sum text box in the first DateValue Footer section.

Just as you can in a text box control on a form, you can define a test against the current value in the control or enter an expression. In this case, verify that Field Value Is appears in the first list, click Greater Than in the second list, and enter the value 400 in the box. Under Condition 1, set Back Color to some dark color, and set Fore Color to white. Click OK to save the conditional format. You can specify a similar condition for the Sum text box in the second DateValue footer (by month) to test for a monthly total greater than 10,000.

Note 

In the Detail section, you can reference any other field in the current row to create an expression. But, when you create a conditional formatting expression in a grouping section, any field reference you use in an expression uses the value of the current row. In a group footer, for example, the current row is the last row displayed in the previous Detail section.

In order for the new formats to appear in Print Preview, you need to make one quick change to the two Sum controls. Select the Sum control in the first DateValue Footer section, hold down the Shift key, and then select the Sum control in the second DateValue Footer section. Open the Property Sheet window, and change the Back Style property from Transparent to Normal. If you leave these controls set to Transparent, you won’t see any text in these controls when the conditions are met.

Before viewing the report one more time, let’s change the margins on the report to center the printed area left to right on the page. Assuming a standard U.S. paper width of 8.5 inches and with the report print area designed at 6.5 inches, we need a 1-inch margin on both sides. Click the Page Setup button in the Page Layout group on the Page Setup tab. Access opens the Page Setup dialog box shown in Figure 16–27. On the Print Options tab, change the Top, Bottom, Left, and Right margins from .25 inch to 1 inch. Click OK to save your changes and close the Page Setup dialog box. When you switch to Print Preview, you can see the result, as shown in Figure 16–28. You can find this sample saved as rptXmplFacilityDateOccupancyStep5. (Page 20 in the sample shows both conditional formats in action.)

Figure 16–27: Change all the page margins to 1 inch.

Figure 16–28: Here is the result of setting conditional formatting for the two Sum text boxes.

To see a more complex example of conditional formatting in action, open rptEmployeeRes in the sample database. That report uses conditional formatting to check for overlapping reservation requests, highlight them, and reveal a warning label in the section header.